INDIRECT and ADDRESS

[Al]

Thanks to Tom Hutchins yesterday for this formula.
=SUMPRODUCT(--(Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(B8:Y8),B8:Y8,0)+1))):INDIRECT(ADDRESS(44,(MATCH(MAX(B8:Y8),B8:Y8,0)+1))))

I need to move this formula to a different sheet and reference the original
sheet (Sheet1) How do I add the reference to Sheet1.
Thanks!

 

[Tom Hutchins]

Try this...

=SUMPRODUCT(--(Sheet1!Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")):INDIRECT(ADDRESS(44,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")))

Hope this helps,

Hutch

 

[Al]

Thanks!

Try this...

=SUMPRODUCT(--(Sheet1!Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")):INDIRECT(ADDRESS(44,(MATCH(MAX(Sheet1!B8:Y8),Sheet1!B8:Y8,0)+1),,,"Sheet1")))

Hope this helps,

Hutch

 

[T. Valko]

=SUMPRODUCT(--(Z9:Z44="CAT"),INDIRECT(ADDRESS(9,(MATCH(MAX(B8:Y8),B8:Y8,0)+1))):INDIRECT(ADDRESS(44,(MATCH(MAX(B8:Y8),B8:Y8,0)+1))))

This will do the same thing:

=SUMPRODUCT(--(Z9:Z44="CAT"),INDEX(B9:Y44,,MATCH(MAX(B8:Y8),B8:Y8,0)+1))

Not sure why you're adding 1 to the MATCH. By adding 1 to the MATCH, if the
match happens to be in Y8 then you'll get an error since that reference is
outside the indexed range.

 

[T. Valko]

Not sure why you're adding 1 to the MATCH.

I think they're using that to calculate the offset of the range from column
A so that the ADDRESS function will return the correct column number.

In the INDEX version you don't need that offset correction:

=SUMPRODUCT(--(Z9:Z44="CAT"),INDEX(B9:Y44,,MATCH(MAX(B8:Y8),B8:Y8,0)))