Max Value Across Worksheets Returning Sheet Name

[bambier]

I have a productivity spreadsheet, where I track the productivity
numbers for a number of individuals. Each worksheet ("tab" if you
prefer) is a different persons name.

Each tab has columns for: A: week#, B: time, items, C: errors, D:
items per hour, E: accuracy.

I know I can find the max value across the sheets by using:
=MAX('1st:End'!D3), but that doesn't tell me which person achieved
that number.

I'd be happy with it either returning the tab name, or the contents of
cell A1.

 

[Guest]

You could use a macro:-

Sub marine()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Maximum = Range("D3").Value
If Maximum > Top Then
Top = Maximum
thename = ActiveSheet.Name
End If
Next ws
MsgBox ("The top seller with " & Top & " Units was " & thename)
End Sub


Mike

 

[Guest]

To return the sheet index containing the largest value try:

=MATCH(1,FREQUENCY(MAX('1st:End'!D3),'1st:End'!D3),0)