Index or Match?

[Albert]

I have the following formula:

=SUMPRODUCT((SummaryAll!$I$5:$I$5000=B5)*(SummaryAll!
$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.

 

[Don Guillett]

Why not just add the date criteria
and dates<=$h$7

 

[Tom Ogilvy]

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")

 

[Albert]

Tht one looks good I'll try it and keep you posted.
Thanks