Return worksheet name containing value found in 3D reference funct

[PKK]

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!

 

[Lori]

If cell C1 in each sheet contains a numerical value, the formula

=MATCH(2,1/FREQUENCY(MAX(Sheet1:Sheet8!C1),Sheet1:Sheet8!C1))

returns the number of the first sheet containing the maximum value.

 

[Peo Sjoblom]

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]

Thanks Lori. That works perfectly for Sheet number. However, let me put in
a new qualifier. What if the sheets are Named and not Sheet1, 2, 3, etc. Do
you know a way to return that name? Perhaps use of the Indirect function?

 

[Bernard Liengme]

A UDF solution that is not as elegant as PKK's formula

Function WheresMax()
mymax = 0
For j = 1 To 8
myvalue = Worksheets(j).Range("C1").Value
If myvalue > mymax Then
mymax = myvalue
mysheet = Worksheets(j).Name
End If
Next j
WheresMax = mysheet
End Function

best wishes

 

[PKK]

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.

 

[Lori]

Maybe create a list of sheet names on a sheet, then use

=INDEX(SheetNames,SheetNumber)

or define "SheetNames" to refer to "=get.workbook(1)" for a list of all
names in the workbook.

 

[Peo Sjoblom]

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]

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!