# Find a max value across multiple sheets and return sheet name

#### XLgeekette

##### New Member
I would like to find the largest value across multiple sheets and return the sheet name where the value is located in. The issue is that each week a new sheet is added so I can't create a static list of sheet names. The data are located in the same cell across sheets in cell B10. I have hidden blank sheets called "First" and "Last" at the beginning and end of each week's sheet ("Week 1" -- "Week N") so I can use the formula MAX(First:Last!B10) to get the largest value. How can get the sheet name where it's located when the sheets are continuously added? Thanks.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Good job on using the formula to span multiple sheets. I just discovered that one last year from somebody else on this site. Unfortunately it won't return which sheet it came from. I suggest a VBA routine for that.

Code:
``````'Provide the name of the first blank hidden sheet
'Provide the name of the last blank hidden sheet
Function GetShtNameWithMax(First As String, Last As String) As String
Dim TWB As Workbook
Dim Sht As Worksheet
Dim Between As Boolean
Dim MaxVal As Double
Dim MaxSht As String
Dim m As Double

Set TWB = ThisWorkbook

For Each Sht In TWB.Worksheets
If UCase(Sht.Name) = UCase(First) Then
Between = True
ElseIf UCase(Sht.Name) = UCase(Last) Then
Between = False
Exit For
ElseIf Between = True Then
m = Application.Max(Sht.Range("C:C"))
If m > MaxVal Then
MaxVal = m
MaxSht = Sht.Name
End If
End If

Next Sht

GetShtNameWithMax = MaxSht

End Function``````

Jeffrey, thanks for the code, but it didn't work. After running it I just get a blank in the cell. Any thoughts on why it's not working?

Hi,

If, as you suggest, your sheets are all consistently named "Week 1", "Week 2", etc.:

="Week "&MATCH(1,FREQUENCY(MAX(First:Last!B10),First:Last!B10),0)

Regards

Thanks XOR LX! I had a make an adjustment to account for the number of sheets in the workbook, but it works.

You're welcome!

Cheers

Replies
3
Views
196
Replies
4
Views
95
Replies
2
Views
236
Replies
1
Views
53
Replies
2
Views
249

1,196,340
Messages
6,014,712
Members
441,838
Latest member
ykg1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back