Find relevant sheet name

[Martin Wheeler]

xl2000

In A1 I have =MIN(Sheet1:Sheet51!K7). Is it possible to show the name for
the sheet this result in B1 (or any other cell)?
Any help would be appreciated.

Ta,
Martin

 

[hgrove]

Martin Wheeler wrote...

In A1 I have =MIN(Sheet1:Sheet51!K7). Is it possible to show
the name for the sheet this result in B1 (or any other cell)?

Not easily. Excel provides very few ways to work with 3D references
and what you're seeking isn't one of the features Excel provides.

If you could live with entering your worksheet names in an array o
range, e.g., entering the following in X1:X5,

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5

you could use the following array formula

=INDEX(X1:X5,
MATCH(MAX(N(INDIRECT("'"&X1:X5&"'!"&CELL("Address",K7)))),
N(INDIRECT("'"&X1:X5&"'!"&CELL("Address",K7))),0)

 

[Martin Wheeler]

Hi hgrove,

Thanks for the reply. I have looked at your formula and I think I'm going
to have to put it in the "too hard basket".
This is not something I need that much, and have 50-60 wkbooks I would use
it in - if it were an easy thing to do. And I would not use it that often.
Thanks again for your help.
Martin