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.
.
|