return worksheet name that a result came from
 

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'!B8:D34) 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.

return worksheet name that a result came from
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"jer130" wrote in message
...
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'!B8:D34) 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.

return worksheet name that a result came from
 

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.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"jer130" wrote in message
...
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'!B8:D34) 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.