Return highest value across multiple sheets as well as sheet name

firemandave

New Member
Joined
Feb 16, 2016
Messages
10
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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))),"")
 

firemandave

New Member
Joined
Feb 16, 2016
Messages
10
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

New Member
Joined
Feb 16, 2016
Messages
10
I did this, but still comes up with that error for some reason.
 

firemandave

New Member
Joined
Feb 16, 2016
Messages
10
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top