Finding a max value in range of cells and returning which sheet that value exists in

[Dannytt93]

Hello,

I am creating a dashboard so that i can see who has the highest value of in a certain column. I can get it to return the value which is great, but is there anyway to get it to return the sheet name too?

e.g. so far i have this:

=MAX('Name1'!N6:N26,'Name2'!N6:N26,'Name3'!N6:N26,'Name4'!N6:N26,'Name5'!N6:N26,'Name6'!N6:N26,'Name7'!N6:N26,'Name8'!N6:N26,'Name9'!N6:N26)


I have tried using Array but due to the names being actual names and not a numerical value, i cant get it to work. i have changed the name of the people (which are the names of the sheets) to name 1 for privacy.

Any help would be great.

Kind Regards

Dan

 

[Special-K99]

Not saying I'll answer this but...

As usual with a MAX() command, what happens if there's a more than one person has the highest value in a certain column?
What would you expect to see then?

 

[Aladin Akyurek]

Book1
	N
5
6	3
7	7
8	3
9	7
10
Name1

Book1
	N
5
6	4
7	1
8	7
9
10
Name2

Book1
	N
5
6	5
7	4
8	2
9
10
Name3

We have a set of 3 sheets for reasons of space.

Book1
	A	B	C
1	Name1		max value
2	Name2		7
3	Name3		list
4			Name1
5			Name1
6			Name2
7
result

Create a range for the relevant sheet names as done in A1:13 above; select this range and name the selection SheetList.

In C2 control+shift+enter, not just enter:

=MAX(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!N2:N26"),ROW($N$2:$N$26)-ROW($N$2),0)))

In C4 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(SheetList,SMALL(IF(TRANSPOSE(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!N2:N26"),ROW($N$2:$N$26)-ROW($N$2),0)))=$C$2,ROW(SheetList)-ROW(INDEX(SheetList,1,1))+1),ROWS($1:1))),"")