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.

Index or Match?
 

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

--
Don Guillett
SalesAid Software

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

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.

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.


.