How fussed are you about doing this with a number of cells?
Assume you have your summary sheet and 4 other sheets,
Summary,Sheet2,Sheet3,Sheet4,Sheet5 and that your values are to be found in the
range A1:A30 on each of sheets 2 to 5
On the summary sheet in cell A2 put
=MID(CELL("filename",Sheet2!$A$1),FIND("]",CELL("filename",Sheet2!$A$1))+1,255)
On the summary sheet in cell A3 put
=MID(CELL("filename",Sheet3!$A$1),FIND("]",CELL("filename",Sheet3!$A$1))+1,255)
On the summary sheet in cell A4 put
=MID(CELL("filename",Sheet4!$A$1),FIND("]",CELL("filename",Sheet4!$A$1))+1,255)
On the summary sheet in cell A5 put
=MID(CELL("filename",Sheet5!$A$1),FIND("]",CELL("filename",Sheet5!$A$1))+1,255)
This will bring back the sheet names, and even if you change the actual names of
the sheets themselves, then they will change here automatically.
Now in cells
B2 =MAX(Sheet2!$A$1:$A$30)
B3 =MAX(Sheet3!$A$1:$A$30)
B4 =MAX(Sheet4!$A$1:$A$30)
B5 =MAX(Sheet5!$A$1:$A$30)
B6 =MAX(B2:B5)
Now in cells
C2 =IF(B2=$B$6,A2,"")
C3 =IF(B3=$B$6,A3,"")
C4 =IF(B4=$B$6,A4,"")
C5 =IF(B5=$B$6,A5,"")
In Col C you will now see a list of all sheets that have a value equal to the
max, and the max value will be next to it in Col B
If you want it to look a bit tidier then move (Not copy) the cell B6 to C6, and
hide or shrink Col B to a very small value.
