Formula to Display Sheet Name of Worksheet that Contains Max Value Across Multiple Sheets in Workbook

[erino]

I have created a workbook that is comparing my business transactions from year to year. Each year is represented by a worksheet (2020, 2019, 2018, 2017, etc.). On each worksheet, I have listed the transactions, and then broken down data for the year including number of transactions, total volume, total commission, average sales price, largest transaction, smallest transaction, average # days of a transaction, etc.

I created an "Overall" sheet for the beginning of the workbook to summarize my whole business. I have been able to figure out the formula for the yearly average, the year that had the highest and the year with the lowest of each statistic. But I for the max and the min, I would like that sheet to display which year (or sheet) those stats came from. I can't figure out a formula to display that? I have spent weeks searching the internet for this formula. PLEASE HELP!

 

[alz]

Hi *erino

if your formula is this:
=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)

Total eight sheets in this formula So in the formula use {1,2,3,4,5,6,7,8}

if added 2021 in your formula :
=MAX('2021'!B61,'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)
total night sheet now So Use {1,2,3,4,5,6,7,8,9} and you have to added '2021'B61 manually to relevent place
everytime you added new sheet you have to edit each formula manually


It seems like you are planning to added more years in your spreed sheets. I suggest to use @Dave Patton Formula which is more robust and efficient.
It just need a few edit for adding new sheet

 

[Dave Patton]

Another example that you can try. Try with my example.
My example has:
- a blank sheet in front of the relevant data sheets; it is named Start
- a blank sheet after the last relevant data sheet; it is named End.
- numbers in cell J4 on each of the data sheets are shown with the formula in F2 etc

To add another sheet (Year information) insert the sheet between Start and End.
Add the Sheet name to the named range of sheets.
To remove a sheet from the calculation, move the sheet so it is not between the Start and End
Add the Sheet name to the named range of sheets.

Alternative
You could assemble the data on your summary sheet and complete the calculations that you require on the summary details
See the Sum and Average in Column F.

3d 2021.xlsm
	A	B	C	D	E	F
1					Sheets	Values in cells
2			Sheet		2017	50
3	Max	50	2017		2018	40
4	Min	20	2020		2019	30
5	Average	35			2020	20
6	2 largest	40	2018		Average	35
7	2 lowest	30	2019		Sum	140
8	Sum	140
9
Summary

Cell Formulas
Range	Formula
C6:C7,C3:C4	C3	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!J4"),B3)>0,0))
F2:F5	F2	=INDIRECT(E2&"!J4")
F6	F6	=AVERAGE(F2:F5)
F7	F7	=SUM(F2:F5)
B3	B3	=MAX(Start:End!J4)
B4	B4	=MIN(Start:End!J4)
B5	B5	=AVERAGE(Start:End!J4)
B6	B6	=LARGE(Start:End!J4,2)
B7	B7	=SMALL(Start:End!J4,2)
B8	B8	=SUM(Start:End!J4)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$E$2:$E$5	C3:C4, C6:C7, F2

 

[erino]

@Dave Patton - I really appreciate your patience with this and with me. Okay, I have added the Start and End sheets. And I have figured out the "Max", "Min", "Average" formulas and making those easier. I can't seen to understand what formula to use to make the year appear in the next cell over. I think it is because I am showing so many examples and you are trying to help me with so much at one time. I think I understand, but I got a #VALUE on the first section, so let me start there . . .

Transaction_Comparison_ELO.xlsx
	A	B	C
12	Avg Sales Price	$ 403,481
13	Best Average Sales Price	$ 527,029	#NAME?
14	Lowest Average Sales Price	$ 294,500	#NAME?
Overall

Cell Formulas
Range	Formula
B12	B12	=C9/B9
B13	B13	=MAX(Start:End!B60)
C13	C13	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B60"),B13)>0,0))
B14	B14	=MIN(Start:End!B60)
C14	C14	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B60"),B14)>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

Did you name your list of sheet names Sheets?
Ensure that you array enter your formula with any changes.

 

[Dave Patton]

re: mini 3 D example

I added Ver 2 This is not an Array Formula
Sheet names are in ascending order
The list of sheet names is named Sheets
The data on each sheet is shown beside the sheet names; one can visually check the results without looking at the sheets.
You can use the above detail or just delete the range; the formulas are volatile.

3d 2021.xlsm
	A	B	C	D	E	F	G
1			Ver 1	Ver 2		Sheets	Values in J4 on each Sheet
2			Sheet	Sheet		2017	50
3	Max	50	2017	2017		2018	40
4	Min	20	2020	2020		2019	30
5	Average	35				2020	20
6	2 largest	40	2018	2018
7	2 lowest	30	2019	2019
8	Sum	140
9	Sum	140
Summary

Cell Formulas
Range	Formula
C6:C7,C3:C4	C3	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!J4"),B3)>0,0))
D6:D7,D3:D4	D3	=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B3,'2017:2020'!$J$4),0) / COUNT('2017:2020'!$J$4)* COUNTA(Sheets),1))
G2:G5	G2	=INDIRECT(F2&"!J4")
B3	B3	=MAX(Start:End!J4)
B4	B4	=MIN(Start:End!J4)
B5	B5	=AVERAGE(Start:End!J4)
B6	B6	=LARGE(Start:End!J4,2)
B7	B7	=SMALL(Start:End!J4,2)
B8	B8	=SUM(Start:End!J4)
B9	B9	=SUM('2017:2020'!J4)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$F$2:$F$5	G2, C6:D7, C3:D4

 

[Dave Patton]

3d 2021.xlsm
	A	B	C	D	E	F
1						Sheets
2				Sheet		2017
3	Max	50		2017		2018
4						2019
5						2020
6
Summary (2)

Cell Formulas
Range	Formula
B3	B3	=MAX(Start:End!J4)
D3	D3	=INDEX(F2:F5,CEILING(MATCH(1,FREQUENCY(B3,'2017:2020'!$J$4),0) / COUNT('2017:2020'!$J$4)* COUNTA(F2:F5),1))

Named Ranges
Name	Refers To	Cells
'Summary (2)'!Sheets	='Summary (2)'!$F$2:$F$5	D3

N.B. The sheets are in ascending order from left to right (2017, 2018, 2019,2020)

To move a sheet, right click on sheet, then select move or copy. click on a cell and the sheet moves before that sheet.

 

[Dave Patton]

With a minor adjustment, the functions in the Array formula will work without Array Enter.
If you select the named range Sheets and press F9, the formula will show the range { .. }
It is more consistent to change both instances of Sheets; but the formula will be longer.

3d 2021.xlsm
	A	B						H	I	J
3	Max	50						2017	2017	2017
Summary

Cell Formulas
Range	Formula
B3	B3	=MAX(Start:End!J4)
H3	H3	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!J4"),B3)>0,0))
I3	I3	=INDEX({2017;2018;2019;2020},MATCH(TRUE,COUNTIF(INDIRECT("'"&{2017;2018;2019;2020}&"'!J4"),B3)>0,0))
J3	J3	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&{2017;2018;2019;2020}&"'!J4"),B3)>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$F$2:$F$5	H3, J3

 

[erino]

So I tried with your adjustments . . . .

I have the Sheets labeled in K1:K9

Transaction_Comparison_ELO.xlsx
	K
1	Sheets
2	2013
3	2014
4	2015
5	2016
6	2017
7	2018
8	2019
9	2020
10
Overall

Then I tried Version 1 and still got Errors

Transaction_Comparison_ELO.xlsx
	A	B	C
12	Avg Sales Price	$ 403,481
13	Best Average Sales Price	$ 527,029	#NAME?
14	Lowest Average Sales Price	$ 294,500	#NAME?
Overall

Cell Formulas
Range	Formula
B12	B12	=C9/B9
B13	B13	=MAX(Start:End!B60)
C13:C14	C13	=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B60"),B13)>0,0))
B14	B14	=MIN(Start:End!B60)
Press CTRL+SHIFT+ENTER to enter array formulas.

Then I tried Version 2 and still got Errors

Transaction_Comparison_ELO.xlsx
	A	B	C
12	Avg Sales Price	$ 403,481
13	Best Average Sales Price	$ 527,029	#NAME?
14	Lowest Average Sales Price	$ 294,500	#NAME?
Overall

Cell Formulas
Range	Formula
B12	B12	=C9/B9
B13	B13	=MAX(Start:End!B60)
C13:C14	C13	=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B13,'2020:2013'!$B$60),0) / COUNT('2020:2013'!$B$60)* COUNTA(Sheets),1))
B14	B14	=MIN(Start:End!B60)

 

[Dave Patton]

Highlight (select) the name in the formula "Sheets" and press F9.
Does the formula show just the range of Sheet names that you show in K2:K9?
Does the formula like =INDEX({2017;2018;2019;2020},MATCH(TRUE,C?OUNTIF(INDIRECT("'"&{2017;2018;2019;2020}&"'!J4"),B3)>0,0)) work for you?

Can you post an extract of your worksheet with the forum's XL2BB?

 

[erino]

So, do I need to write out {2013;2014;2015;2016;2017;2018;2019;2020} every time it says Sheets in this formula below?

=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B3,'2017:2020'!$J$4),0) / COUNT('2017:2020'!$J$4)* COUNTA(Sheets),1))


There is a lot more to the sheet and if I try to copy too much it won't let me, but it let me copy this portion which gets you that section and the part that has the sheets:

Transaction_Comparison_ELO.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Lead Origin All Sale Trans:	#	Volume	%	Commission	%							1/1/13
2	Sphere	69	$ 27,952,474	37.25%	$ 385,284	38.08%
3	Previous Client	39	$ 15,523,330	20.68%	$ 216,974	21.44%							Sheets
4	Referral	56	$ 23,228,493	30.95%	$ 300,321	29.68%							2013
5	Reputation	2	$ 994,000	1.32%	$ 14,748	1.46%							2014
6	Listing Lead	15	$ 5,494,100	7.32%	$ 72,611	7.18%							2015
7	HH Relocation	2	$ 937,000	1.25%	$ 5,519	0.55%							2016
8	Facebook/Insta/Online ?	3	$ 918,000	1.22%	$ 16,320	1.61%							2017
9	TOTALS	186	$ 75,047,397		$ 1,011,776								2018
10													2019
11													2020
12	Avg Sales Price	$ 403,481			Avg # of Days	111.44
13	Best Average Sales Price	$ 527,029	#NAME?	2016	Max # of Days	1263
14	Lowest Average Sales Price	$ 294,500	#NAME?	2014	Min # of Days	0
15
16	Largest Transaction	$ 1,425,000.00		2020	TowneBank	Nick Russo
17	Smallest Transaction	$ 45,200.00		2013	Total #	34
Overall

Cell Formulas
Range	Formula
B2:B8	B2	=SUM('2020'!C51+'2019'!C51+'2018'!C51+'2017'!C51+'2016'!C51+'2015'!C51+'2014'!C51+'2013'!C51+'2021'!C51)
C2:C8,E2:E8	C2	=SUM('2021'!H51+'2020'!H51+'2019'!H51+'2018'!H51+'2017'!H51+'2016'!H51+'2015'!H51+'2014'!H51+'2013'!H51)
D2:D8,F2:F8	D2	=C2/C$9
B9:C9,E9	B9	=SUM(B2:B8)
B12	B12	=C9/B9
B13	B13	=MAX(Start:End!B60)
C13:C14	C13	=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B13,'2020:2013'!$B$60),0) / COUNT('2020:2013'!$B$60)* COUNTA(Sheets),1))
B14	B14	=MIN(Start:End!B60)
F12	F12	=AVERAGE('2021'!G6:G35,'2020'!G$6:G$46,'2019'!G$6:G$31,'2018'!G$6:G$30,'2017'!G6:G26,'2016'!G6:G22,'2015'!G6:G24,'2014'!G6:G20,'2013'!G8:G26)
F13	F13	=MAX('2021'!G6:G35,'2020'!G$6:G$46,'2019'!G$6:G$31,'2018'!G$6:G$30,'2017'!G6:G26,'2016'!G6:G22,'2015'!G6:G24,'2014'!G6:G20,'2013'!G8:G26)
F14	F14	=MIN('2021'!G6:G35,'2020'!G$6:G$46,'2019'!G$6:G$31,'2018'!G$6:G$30,'2017'!G6:G26,'2016'!G6:G22,'2015'!G6:G24,'2014'!G6:G20,'2013'!G8:G26)
B16	B16	=MAX('2021'!B69,'2020'!B69,'2019'!B69,'2018'!B69,'2017'!B69,'2016'!B69,'2015'!B69,'2014'!B69,'2013'!B69)
B17	B17	=MIN('2021'!B70,'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)
F17	F17	=SUM('2021'!C37,'2020'!C48,'2019'!C33,'2018'!C32,'2017'!C28,'2016'!C24,'2015'!C26,'2014'!C22,'2013'!C28)