Retrieving Worksheet Name

[Jim A]

I am trying to find a function that will retrieve a worksheet name if the MAX
function retrieves a number from that work sheet. Any Ideas?

 

[CLR]

Please post your MAX formula.

Vaya con Dios,
Chuck, CABGx3

 

[A]

Hi ,
One convoluted way to pickup the name is to use the address command to
pickup the reference to the cell that contains the number. Once you get
that it will contain the worksheet name and then use string functions
like len,mid to cut string till the ! and then display the string upto
the !

 

[Harlan Grove]

Jim A wrote...

I am trying to find a function that will retrieve a worksheet name if the MAX
function retrieves a number from that work sheet. Any Ideas?

Presumably your MAX function operates over a 3D block, e.g.,
=MAX(first:last!B2:X99), and you want to know the first worksheet in
which the max value appears? Note that in general there could be
multiple instances of the max or min values, so the approach differs
depending on which one you want.

Assuming you want the first worksheet, the easiest way involves using a
list of the worksheet names in order of appearance in the workbook. If
you had such a list named WSL, you could use an array formula like

=INDEX(WSL,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSL&"'!B2:X99"),
MAX(first:last!B2:X99))>0,0))

 

[Gary''s Student]

This is only an example, but you can adapt it for your use:

Say we have a workbook with three worksheets: Sheet1, Sheet, and Sheet3.
Say we have defined some Named Ranges on the sheets:

first for Sheet1!A13
second for Sheet2!A13
third for Sheet3!A13

anywhere in the workbook we enter:
=MAX(first,second,third)
and see displayed 27 (because B2 on Sheet3 contains 27 and it’s the max
across the three sheets)

Enter this UDF:

Function whatsheet(v As Variant, r1 As Range, r2 As Range, r3 As Range) As
String
Dim r As Range

For Each r In r1
If r.Value = v Then
whatsheet = r.Parent.Name
Exit Function
End If
Next

For Each r In r2
If r.Value = v Then
whatsheet = r.Parent.Name
Exit Function
End If
Next

For Each r In r3
If r.Value = v Then
whatsheet = r.Parent.Name
Exit Function
End If
Next

whatsheet = ""

End Function

And =whatsheet(27,first,second,third) will return Sheet3
To find out which cell has the 27, enter and use:

Function whatcell(v As Variant, r1 As Range, r2 As Range, r3 As Range) As
String
Dim r As Range

For Each r In r1
If r.Value = v Then
whatcell = r.Address
Exit Function
End If
Next

For Each r In r2
If r.Value = v Then
whatcell = r.Address
Exit Function
End If
Next

For Each r In r3
If r.Value = v Then
whatcell = r.Address
Exit Function
End If
Next

whatcell = ""

End Function

 

[Harlan Grove]

This is only an example, but you can adapt it for your use:

Really? How would one adapt it if there were more than 29 worksheets
involved?

Say we have a workbook with three worksheets: Sheet1, Sheet, and Sheet3.
Say we have defined some Named Ranges on the sheets:

first for Sheet1!A13
second for Sheet2!A13
third for Sheet3!A13

anywhere in the workbook we enter:
=MAX(first,second,third)
and see displayed 27 (because B2 on Sheet3 contains 27 and it's the max
across the three sheets)

If Sheet1 through Sheet3 were adjacent, wouldn't one use
MAX(Sheet1:Sheet3!A13)?

Enter this UDF:

....

Why bother? The same result could be obtained from the array formula

=INDEX({"Sheet1";"Sheet2";"Sheet3"},MATCH(TRUE,
COUNTIF(INDIRECT({"Sheet1";"Sheet2";"Sheet3"}&"!A13"),
MAX(Sheet1!A13,Sheet2!A13,Sheet3!A13))>0,0))

which could be expanded to any number of worksheets just by changing
the array of worksheet names.

And =whatsheet(27,first,second,third) will return Sheet3
To find out which cell has the 27, enter and use:

Function whatcell(v As Variant, r1 As Range, r2 As Range, r3 As Range) As String ....
For Each r In r1
If r.Value = v Then
whatcell = r.Address
Exit Function
End If
Next

....

Here's where it gets bizarre. Why not change that Address call to
Address(0,0, , 1), which would return the full address of the first
cell matching v, thus giving worksheet name and cell address in one udf
call?