Find address

[RJW999]

I have a formula using a 3D range, =max(Oct:Sep!AB124). Is there anyway I can
return the address of the max value, which could be on any one of 12
worksheets? Formulae based on Address+Index don't work with a 3D range.

 

[Shane Devenshire]

Hi,

For the example you gave:

=IF(Oct!AB24=MAX(Oct!AB24,Sep!AB24),"Oct!AB24","Sep!AB24")

Can't use most of Excel's functions across 3D.

 

[DK]

Here's an attempt to accomplish your task. It works, with a few assumptions:

There are 12 sheets, Jan-Dec. and based on your formula, you're referring to
cell "AB124" on all 12 sheets. Should the sheet Order or Names change, it
would be necessary to modify the list and formula.



On a Summary sheet, in cells A1:A12, list the other 12 sheet names in the
order they appear in the WB.

In cell B1 enter the formula =INDIRECT(A1&"!"&"AB124") Copy down to
B12.

In another cell on the same sheet enter the formula

=INDEX($A$1:$B$12,MATCH(MAX(Janec!$AB$124),$B$1:$B$12,0),1)&"!"&"AB124"

This matches the FIRST occurrence of MAX(Janec!$AB$124) with the values
in B1:B12 and returns the corresponding sheet name along with the text
"!AB124".

This may be a bit crude but it may get you started with a solution.

Best wishes.

DK