# Return highest value across multiple sheets as well as sheet name

#### firemandave

##### New Member
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

##### MrExcel MVP
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

##### Board Regular
May I know the use of AN64?

##### MrExcel MVP
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

##### New Member
=MAX(N(INDIRECT("'"&SheetList&"'!AN64")))
Hi Aladin! Sorry for the delay in my reply as I've been out of action unwell.
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!

##### MrExcel MVP
Hi Aladin! Sorry for the delay in my reply as I've been out of action unwell.
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

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

#### firemandave

##### New Member
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!!

Last edited:

##### MrExcel MVP
Change

=SUM(AM64/AL64)

to either

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

or

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

1,082,281
Messages
5,364,216
Members
400,786
Latest member
ismi88

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...