INDIRECT and ADDRESS
 

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!

INDIRECT and ADDRESS
 

Try this...

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

Hope this helps,

Hutch

"Al" wrote:

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!

INDIRECT and ADDRESS
 

Thanks!

"Tom Hutchins" wrote:

Try this...

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

Hope this helps,

Hutch

"Al" wrote:

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!

INDIRECT and ADDRESS
 

=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:Y 8,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.

--
Biff
Microsoft Excel MVP


"Al" wrote in message
...
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!

INDIRECT and ADDRESS
 

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:Y 8,0)))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=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:Y 8,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.

--
Biff
Microsoft Excel MVP


"Al" wrote in message
...
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!