Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index or Match?
I have the following formula:
=SUMPRODUCT((SummaryAll!$I$5:$I$5000=B5)*(SummaryA ll! $J$5:$J$5000=C5)*(SummaryAll!$K$5:$K$5000=D5)*(Sum maryAll! $G$5:$G$5000=$H$7)*(SummaryAll!$L$5:$L$5000)) This formula calculates an amount based on all the criteria in the sumproduct statements. In order not to recalculate values that will not change, I need to amend the cells reference in $I$5,$J$5,$K$5 etc to be greater or equal to a given date ($H$7). In other words, how can I change "$I$5" to, let's say, "$I$234" which is today's first row that is equal to today's date and so on..... The column to scan will be (SummaryAll!$I$5:$I$5000) and i need to find the first row where the date is equal or greater that today. Thanks in advance for any hint. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index or Match?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index or Match?
You would have to build all your ranges dynamically using indirect. You can
find where the row starts using Match. Indirect("SummaryAll!I" & match(H7,$G$5:$G$5000,0)+4 & ":I5000") -- Regards, Tom Ogilvy "Albert" wrote in message ... I have the following formula: =SUMPRODUCT((SummaryAll!$I$5:$I$5000=B5)*(SummaryA ll! $J$5:$J$5000=C5)*(SummaryAll!$K$5:$K$5000=D5)*(Sum maryAll! $G$5:$G$5000=$H$7)*(SummaryAll!$L$5:$L$5000)) This formula calculates an amount based on all the criteria in the sumproduct statements. In order not to recalculate values that will not change, I need to amend the cells reference in $I$5,$J$5,$K$5 etc to be greater or equal to a given date ($H$7). In other words, how can I change "$I$5" to, let's say, "$I$234" which is today's first row that is equal to today's date and so on..... The column to scan will be (SummaryAll!$I$5:$I$5000) and i need to find the first row where the date is equal or greater that today. Thanks in advance for any hint. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index or Match?
Tht one looks good I'll try it and keep you posted.
Thanks -----Original Message----- You would have to build all your ranges dynamically using indirect. You can find where the row starts using Match. Indirect("SummaryAll!I" & match(H7,$G$5:$G$5000,0)+4 & ":I5000") -- Regards, Tom Ogilvy "Albert" wrote in message ... I have the following formula: =SUMPRODUCT((SummaryAll!$I$5:$I$5000=B5)*(SummaryA ll! $J$5:$J$5000=C5)*(SummaryAll!$K$5:$K$5000=D5)* (SummaryAll! $G$5:$G$5000=$H$7)*(SummaryAll!$L$5:$L$5000)) This formula calculates an amount based on all the criteria in the sumproduct statements. In order not to recalculate values that will not change, I need to amend the cells reference in $I$5,$J$5,$K$5 etc to be greater or equal to a given date ($H$7). In other words, how can I change "$I$5" to, let's say, "$I$234" which is today's first row that is equal to today's date and so on..... The column to scan will be (SummaryAll!$I$5:$I$5000) and i need to find the first row where the date is equal or greater that today. Thanks in advance for any hint. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |