How to return the sheet name of the cell that = max?

  • Thread starter Thread starter Gem
  • Start date Start date
G

Gem

I want to find the max across different sheets

MAX(Sheet1!A:A,Sheet2!A:A,Sheet3!A:A)

and the MAX is in Sheet2's cell A3. How can I return the name of th
sheet name where the MAX value is located (Sheet2)
 
One way:

=MID(IF(MAX(Sheet1:Sheet3!A:A)=MAX(Sheet1!A:A),
CELL("filename",Sheet1!A1), IF(MAX(Sheet1:Sheet3!A:A)=MAX(Sheet2!A:A),
CELL("filename",Sheet2!A1), CELL("filename",Sheet3!A:A))),
FIND("]",CELL("filename",Sheet1!A1))+1, 255)

which will survive the renaming of sheets. Note that the workbook must
be saved for this to work.
 
Back
Top