Return highest value across multiple sheets as well as sheet name

[firemandave]

Hi all!

I've been trying to get this one to work for a while now to no avail. I've googled and searched forums, tried all sorts of formulas and just keep running into walls and errors

I have a workbook with multiple sheets of staff performance figures. Each staff member's sheet is the same (ie highest $ is always F6 for example) and itself draws data from another workbook. I want to be able to quickly see which staff member has the highest performance figure across a number of values.
All sheets are named after the staff member, so I've created a START sheet (at the start) and and END sheet (of course, at the end).
In order to find the highest value, I've tried formulas such as =MAX(START:END!AN46) (where cell AN46 is the performance figures I'm wanting to compare, but it just results in #VALUE ! errors. Some staff might not have a score for a certain performance figure, so not sure if I need to work in an IFERROR statement? Tried =AGGREGATE(4,6,START:END!AN64) to no avail. If I replace START:END! with the name of each individual sheet (separated by comas) it will return the highest value. Not sure what's going on there...
In order to show the sheet name, I've tried =INDEX(A1:A36,MATCH(MAX(N(INDIRECT("'"&A1:A36&"'!AN64"))),N(INDIRECT("'"&A1:A36&"'!AN64")),0)) where I listed all sheet names from A1 to A36 which also returned #VALUE ! but I'm pretty sure I've missed something there :P

This is doing my head in... help please!

 

[firemandave]

Brilliant! Now that solved it. Thank you so much. Never stop learning....

 

[Aladin Akyurek]

Brilliant! Now that solved it. Thank you so much. Never stop learning....

You are welcome. Thanks for the update.