software

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?

Please post your MAX formula.

Vaya con Dios,
Chuck, CABGx3quot;Jim Aquot; lt;u20100@uwegt; wrote in message news:5dc89f7286d78@uwe...
gt; I am trying to find a function that will retrieve a worksheet name if the
MAX
gt; function retrieves a number from that work sheet. Any Ideas?
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 = quot;quot;

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 = quot;quot;

End Function

--
Gary's Studentquot;Jim Aquot; wrote:

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