return worksheet name that a result came from

[jer130]

i have 5 worksheets in a book that i reference.Each worksheet has a name.I
have bowling scores for each person .when i do a search to find who has the
high score i can get that result no problem.It will give me a result 298
which turns out right.
My problem is i don't know the formula to add to this to tell me what
worksheet the score came from.
eg.. john has the high score of 298
"has the high score of " I can do and the result 298 returns
automatically.the sheet name is jerry but i don't know a formula to do this
and i am sure there is one.
=MAX('jerry:rob w'!B834) this is the formula to find who has the high
score between the 5 sheets which each has its own name jerry to rob..I am
reading the help in excel since last year but i just can't figure out this
one.If ther is anyone who can help me I would appreciate it. Thanks.
(e-mail address removed)

 

[Bernard Liengme]

Not very sophisticated but this macro should do it.

Sub findmax()
mymax = 0
For Each ws In Worksheets
'MsgBox ws.Name
If ws.Name <> "Summary" Then
If ws.range("a1").Value > mymax Then
mymax = ws.range("a1")
temp = ws.Name
End If
End If
Next
Worksheets("Summary").Activate
range("B2") = "max found on" & temp
End Sub

best wishes

 

[jer130]

I will play with this info a bit and see if i can get anything to work.I am
not sure if i use this whole equation with the max function to find the high
score but i will try a few things .Thanks for replying.