Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Indirect(address...function help? | Excel Worksheet Functions | |||
using INDIRECT(ADDRESS(...)) | Excel Discussion (Misc queries) | |||
Indirect(Address(... | Excel Discussion (Misc queries) | |||
INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions |