If you have totally unrelated sheet names of the sheets or rather no number
index with the same string you could put all the sheet names (ALL sheet
names, not just first and last) in a range, assume we call that range
MySheets

=INDEX(MySheets,MATCH(MAX(N(INDIRECT("'"&MySheets& "'!C1"))),N(INDIRECT("'"&MySheets&"'!C1")),0))

entered with ctrl + shift & enter

However Lori's solution is better and would work with even less tweaking,
assume as above the list with all sheet names is called MySheets

=INDEX(MySheets,MATCH(2,1/FREQUENCY(MAX(abc:vbnmh!C1),abc:vbnmh!C1)))

will still work plus it is non volatile as opposed to my formula with
INDIRECT




--


Regards,


Peo Sjoblom






"PKK" wrote in message
...
Thank you Peo. This works perfectly as well. Now I just need to get it
working where the sheets are now named instead of the default Sheet1, 2, 3
etc.

"Peo Sjoblom" wrote:

If you can use sheet names like in your example with the same text string
then just different index number it isn't that hard

="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("' Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0)

entered with ctrl + shift & enter will return the sheet name that holds
the
MAX value, note that if there are multiple max values the first
occurrence
will be returned. That should answer your second question


--


Regards,


Peo Sjoblom




"PKK" wrote in message
...
I'm trying to write a function that returns the worksheet name of a max
value
in a simple 3d reference.

Sheets 1 through 8 hold values in cell C1. In a summary page
=MAX(Sheet1:Sheet8!C1) returns the max value found in any of those
cells.
Can a function return the sheet name of the sheet on which that max
value
was
found?

Additionally, what errors might be generated if the Max value is on
more
than one sheet?

Thank you!

Exactly, that's what I ended up doing. Thanks both Lori and Peo!

My only challenge for the future though, is being able to do this with named
sheets and no indexing. Since indexing simply returns a text string as
typed, it isn't really a reference to the tab in question. As such, it
limits options to go and use that return in other functions. However, for
now it really helps a lot. Thanks again!

"Peo Sjoblom" wrote:

If you have totally unrelated sheet names of the sheets or rather no number
index with the same string you could put all the sheet names (ALL sheet
names, not just first and last) in a range, assume we call that range
MySheets

=INDEX(MySheets,MATCH(MAX(N(INDIRECT("'"&MySheets& "'!C1"))),N(INDIRECT("'"&MySheets&"'!C1")),0))

entered with ctrl + shift & enter

However Lori's solution is better and would work with even less tweaking,
assume as above the list with all sheet names is called MySheets

=INDEX(MySheets,MATCH(2,1/FREQUENCY(MAX(abc:vbnmh!C1),abc:vbnmh!C1)))

will still work plus it is non volatile as opposed to my formula with
INDIRECT




--


Regards,


Peo Sjoblom






"PKK" wrote in message
...
Thank you Peo. This works perfectly as well. Now I just need to get it
working where the sheets are now named instead of the default Sheet1, 2, 3
etc.

"Peo Sjoblom" wrote:

If you can use sheet names like in your example with the same text string
then just different index number it isn't that hard

="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("' Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0)

entered with ctrl + shift & enter will return the sheet name that holds
the
MAX value, note that if there are multiple max values the first
occurrence
will be returned. That should answer your second question


--


Regards,


Peo Sjoblom




"PKK" wrote in message
...
I'm trying to write a function that returns the worksheet name of a max
value
in a simple 3d reference.

Sheets 1 through 8 hold values in cell C1. In a summary page
=MAX(Sheet1:Sheet8!C1) returns the max value found in any of those
cells.
Can a function return the sheet name of the sheet on which that max
value
was
found?

Additionally, what errors might be generated if the Max value is on
more
than one sheet?

Thank you!