Retrieving Worksheet Name
 

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?

Retrieving Worksheet Name
 

Please post your MAX formula.

Vaya con Dios,
Chuck, CABGx3


"Jim A" <u20100@uwe wrote in message news:5dc89f7286d78@uwe...
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?

Retrieving Worksheet Name
 

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!A1:D3
second for Sheet2!A1:D3
third for Sheet3!A1:D3

anywhere in the workbook we enter:
=MAX(first,second,third)
and see displayed 27 (because B2 on Sheet3 contains 27 and its 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

--
Gary's Student


"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?