Reference the worksheet from a multiple worksheet range function (
 

I want to reference the worksheet that is returned when I do a 3-D reference
function.

For example:
If on worksheet "1" of 40 I have this formula:
=MAX('2:40'!I4)

It will return the maximum value, but I need to know which worksheet had the
maximum value. Suggestions? Please?

Hi!

Probably not what you had in mind ....

List the sheet names somewhere, say, starting in H2.

In H2 enter 2. In H3 enter 3.

Select both H2 and H3 and drag down to H40.

In I2 enter this formula and copy down to I40:

=INDIRECT(H2&"!I4")

To find the sheet with the max value from cell I4:

=INDEX(H2:H40,MATCH(MAX(I2:I40),I2:I40,0))

Biff

"DBickel" wrote in message
...
I want to reference the worksheet that is returned when I do a 3-D
reference
function.

For example:
If on worksheet "1" of 40 I have this formula:
=MAX('2:40'!I4)

It will return the maximum value, but I need to know which worksheet had
the
maximum value. Suggestions? Please?