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!

 

[Aladin Akyurek]

Let's have 3 sheets, named jon, damon, and Nathan, with AN64 equaling 7, 9, and 9.

Row\Col	A​
1​	SheetList
2​	jon
3​	damon
4​	nathan
5​	Max
6​	9​
7​	damon
8​	nathan
9​

1. Name A2:A4 SheetList in Name Manager.

2. In A6 control+shift+enter, not just enter:

=MAX(N(INDIRECT("'"&SheetList&"'!AN64")))

3. In A7 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(SheetList,SMALL(IF(N(INDIRECT("'"&SheetList&"'!AN64"))=$A$6,ROW(SheetList)-ROW(INDEX(SheetList,1,1))+1),ROWS($A$7:A7))),"")

 

[ryan0521]

May I know the use of AN64?

 

[Aladin Akyurek]

May I know the use of AN64?

The data of the OP consists of numbers in AN64 (the target cell) of each sheet listed in SheetList.

 

[firemandave]

=MAX(N(INDIRECT("'"&SheetList&"'!AN64")))

Hi Aladin! Sorry for the delay in my reply as I've been out of action unwell.
Thank you very much for your detailed reply!
I am sure however that there must be something wrong with my data, as when I use the above formula I am still receiving a "#VALUE!" error
Not every sheet has a value for AN64... is that the cause? If so, I'm sorry I never mentioned it in the original post!

 

[Aladin Akyurek]

Hi Aladin! Sorry for the delay in my reply as I've been out of action unwell.
Thank you very much for your detailed reply!
I am sure however that there must be something wrong with my data, as when I use the above formula I am still receiving a "#VALUE!" error
Not every sheet has a value for AN64... is that the cause? If so, I'm sorry I never mentioned it in the original post!

You need to apply control+shift+enter to the suggested formulas. That means: Press down the control and the shift keys at the same time while you hit the enter kay. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

 

[firemandave]

I did this, but still comes up with that error for some reason.

 

[Aladin Akyurek]

I did this, but still comes up with that error for some reason.

All should work as advertised. See the workbook which implements the set up: https://www.dropbox.com/s/u9qaox7rjkjhfmt/firemandave across sheets.xlsx?dl=0

 

[firemandave]

For some sheets in AN64 there is no value, as AN64 is a calculation between two other cells. Of these two other cells, one may be an error (as it itself is a linked value). This then results in a "#VALUE!" error that wrecks everything.
EG These formulas...

=IF(ISERROR(('WK 1'!$O$56)),"",('WK 1'!$O$56))

=IF(ISERROR(('WK 2'!$O$56)),"",('WK 2'!$O$56))

=SUM(AM64/AL64)

<tbody>
</tbody>

...produce these results.

$32.50

#VALUE!

<tbody>
</tbody>

Sorry for my dodgy tables!!

 

[Aladin Akyurek]

Change

=SUM(AM64/AL64)

to either

=IF(ISNUMBER(1/(AM64*AL64)),AM64/AL64,"")

or

=IF(ISNUMBER(1/(AM64*AL64)),AM64/AL64,0)