tag:blogger.com,1999:blog-1491808870172686034.post7459495552188024435..comments2021-04-24T16:27:09.118+05:30Comments on ExcelMatic: Solve this email query - populating values based on max valueUnknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-1491808870172686034.post-2576515618212092012009-07-30T19:13:50.488+05:302009-07-30T19:13:50.488+05:30Guys Pretty Simple and good logic..
PrakashGuys Pretty Simple and good logic..<br /><br />PrakashAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1491808870172686034.post-80454335398144285052009-07-18T02:07:32.633+05:302009-07-18T02:07:32.633+05:30Here is my code for the same query:
=INDIRECT(ADD...Here is my code for the same query:<br /><br />=INDIRECT(ADDRESS(ROW($I19),MATCH(MAX($K$2:$DE$2),$A$2:$DE$2,0)))Excel Matichttp://excelmatic.blogspot.comnoreply@blogger.comtag:blogger.com,1999:blog-1491808870172686034.post-56539195509177136762009-07-18T02:03:43.005+05:302009-07-18T02:03:43.005+05:30@hp @Jason @Anonymous - guys great work! it works ...<b>@hp @Jason @Anonymous</b> - guys great work! it works really great. The code has also taken care of inserting a new row between rows 2 and 9. Thanks guys.<br /><br /><b>@Jason @Anonymous</b> - what a co-incidence!! You both guys submitted exactly the same code ;-)Excel Matichttp://excelmatic.blogspot.comnoreply@blogger.comtag:blogger.com,1999:blog-1491808870172686034.post-85997930610191879352009-07-17T20:00:05.115+05:302009-07-17T20:00:05.115+05:30Try this:
INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1...Try this:<br /><br />INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1),MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))<br /><br />This will work even if more blank rows are inserted before row 2 and between rows 2 and 19.<br /><br />JasonAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1491808870172686034.post-46431036830551120352009-07-17T19:58:48.130+05:302009-07-17T19:58:48.130+05:30Insert the following formula into cell G19 and cop...Insert the following formula into cell G19 and copy down:<br /><br />INDEX($J$2:$DD$166,ROW(F19)-(ROW(G$2)-1),MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))<br /><br />This should still work even if more blank rows are inserted before row 2 or between rows 2 and 19.<br /><br />Jason<br />---Anonymoushttps://www.blogger.com/profile/07704120258170841615noreply@blogger.comtag:blogger.com,1999:blog-1491808870172686034.post-45958965179156408752009-07-17T02:12:19.982+05:302009-07-17T02:12:19.982+05:30Hello,
You may use this formula: "INDEX($J$19...Hello,<br />You may use this formula: <b>"INDEX($J$19:$GZ$166,ROW()-18,MATCH(MAX($J$2:$DD$2),$J$2:$DD$2,0))" </b> in cell G19 and then drag the formula till where you want the data to be copied. Now whenever the data in row 2 changes the data in column G will be populated with the data from the column where the highest value exists in row 2 (the data will be pulled from the range $J$19:$GZ$166).<br /><br />Hope this solves Pedro's problem!!<br /><br />Thanks,<br />hpAnonymoushttps://www.blogger.com/profile/10194245023623884122noreply@blogger.com