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

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
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

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDEF
1SheetsValues in cells
2Sheet201750
3Max502017201840
4Min202020201930
5Average35202020
62 largest402018Average35
72 lowest302019Sum140
8Sum140
9
Summary
Cell Formulas
RangeFormula
C6:C7,C3:C4C3=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!J4"),B3)>0,0))
F2:F5F2=INDIRECT(E2&"!J4")
F6F6=AVERAGE(F2:F5)
F7F7=SUM(F2:F5)
B3B3=MAX(Start:End!J4)
B4B4=MIN(Start:End!J4)
B5B5=AVERAGE(Start:End!J4)
B6B6=LARGE(Start:End!J4,2)
B7B7=SMALL(Start:End!J4,2)
B8B8=SUM(Start:End!J4)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Sheets=Summary!$E$2:$E$5C3:C4, C6:C7, F2
 
Last edited:

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
@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
ABC
12Avg Sales Price $ 403,481
13Best Average Sales Price$ 527,029#NAME?
14Lowest Average Sales Price$ 294,500#NAME?
Overall
Cell Formulas
RangeFormula
B12B12=C9/B9
B13B13=MAX(Start:End!B60)
C13C13=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B60"),B13)>0,0))
B14B14=MIN(Start:End!B60)
C14C14=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B60"),B14)>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Did you name your list of sheet names Sheets?
Ensure that you array enter your formula with any changes.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFG
1Ver 1Ver 2SheetsValues in J4 on each Sheet
2SheetSheet201750
3Max5020172017201840
4Min2020202020201930
5Average35202020
62 largest4020182018
72 lowest3020192019
8Sum140
9Sum140
Summary
Cell Formulas
RangeFormula
C6:C7,C3:C4C3=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!J4"),B3)>0,0))
D6:D7,D3:D4D3=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B3,'2017:2020'!$J$4),0) / COUNT('2017:2020'!$J$4)* COUNTA(Sheets),1))
G2:G5G2=INDIRECT(F2&"!J4")
B3B3=MAX(Start:End!J4)
B4B4=MIN(Start:End!J4)
B5B5=AVERAGE(Start:End!J4)
B6B6=LARGE(Start:End!J4,2)
B7B7=SMALL(Start:End!J4,2)
B8B8=SUM(Start:End!J4)
B9B9=SUM('2017:2020'!J4)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Sheets=Summary!$F$2:$F$5G2, C6:D7, C3:D4
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
3d 2021.xlsm
ABCDEF
1Sheets
2Sheet2017
3Max5020172018
42019
52020
6
Summary (2)
Cell Formulas
RangeFormula
B3B3=MAX(Start:End!J4)
D3D3=INDEX(F2:F5,CEILING(MATCH(1,FREQUENCY(B3,'2017:2020'!$J$4),0) / COUNT('2017:2020'!$J$4)* COUNTA(F2:F5),1))
Named Ranges
NameRefers ToCells
'Summary (2)'!Sheets='Summary (2)'!$F$2:$F$5D3



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

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABHIJ
3Max50201720172017
Summary
Cell Formulas
RangeFormula
B3B3=MAX(Start:End!J4)
H3H3=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!J4"),B3)>0,0))
I3I3=INDEX({2017;2018;2019;2020},MATCH(TRUE,COUNTIF(INDIRECT("'"&{2017;2018;2019;2020}&"'!J4"),B3)>0,0))
J3J3=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&{2017;2018;2019;2020}&"'!J4"),B3)>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Sheets=Summary!$F$2:$F$5H3, J3
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
So I tried with your adjustments . . . .

I have the Sheets labeled in K1:K9

Transaction_Comparison_ELO.xlsx
K
1Sheets
22013
32014
42015
52016
62017
72018
82019
92020
10
Overall


Then I tried Version 1 and still got Errors

Transaction_Comparison_ELO.xlsx
ABC
12Avg Sales Price $ 403,481
13Best Average Sales Price$ 527,029#NAME?
14Lowest Average Sales Price$ 294,500#NAME?
Overall
Cell Formulas
RangeFormula
B12B12=C9/B9
B13B13=MAX(Start:End!B60)
C13:C14C13=INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B60"),B13)>0,0))
B14B14=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
ABC
12Avg Sales Price $ 403,481
13Best Average Sales Price$ 527,029#NAME?
14Lowest Average Sales Price$ 294,500#NAME?
Overall
Cell Formulas
RangeFormula
B12B12=C9/B9
B13B13=MAX(Start:End!B60)
C13:C14C13=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B13,'2020:2013'!$B$60),0) / COUNT('2020:2013'!$B$60)* COUNTA(Sheets),1))
B14B14=MIN(Start:End!B60)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLM
1Lead Origin All Sale Trans:#Volume%Commission%1/1/13
2Sphere69$ 27,952,47437.25%$ 385,28438.08%
3Previous Client39$ 15,523,33020.68%$ 216,97421.44%Sheets
4Referral56$ 23,228,49330.95%$ 300,32129.68%2013
5Reputation2$ 994,0001.32%$ 14,7481.46%2014
6Listing Lead15$ 5,494,1007.32%$ 72,6117.18%2015
7HH Relocation2$ 937,0001.25%$ 5,5190.55%2016
8Facebook/Insta/Online ?3$ 918,0001.22%$ 16,3201.61%2017
9TOTALS186$ 75,047,397$ 1,011,7762018
102019
112020
12Avg Sales Price $ 403,481Avg # of Days111.44
13Best Average Sales Price$ 527,029#NAME?2016Max # of Days1263
14Lowest Average Sales Price$ 294,500#NAME?2014Min # of Days0
15
16Largest Transaction$ 1,425,000.002020TowneBankNick Russo
17Smallest Transaction$ 45,200.002013Total #34
Overall
Cell Formulas
RangeFormula
B2:B8B2=SUM('2020'!C51+'2019'!C51+'2018'!C51+'2017'!C51+'2016'!C51+'2015'!C51+'2014'!C51+'2013'!C51+'2021'!C51)
C2:C8,E2:E8C2=SUM('2021'!H51+'2020'!H51+'2019'!H51+'2018'!H51+'2017'!H51+'2016'!H51+'2015'!H51+'2014'!H51+'2013'!H51)
D2:D8,F2:F8D2=C2/C$9
B9:C9,E9B9=SUM(B2:B8)
B12B12=C9/B9
B13B13=MAX(Start:End!B60)
C13:C14C13=INDEX(Sheets,CEILING(MATCH(1,FREQUENCY(B13,'2020:2013'!$B$60),0) / COUNT('2020:2013'!$B$60)* COUNTA(Sheets),1))
B14B14=MIN(Start:End!B60)
F12F12=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)
F13F13=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)
F14F14=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)
B16B16=MAX('2021'!B69,'2020'!B69,'2019'!B69,'2018'!B69,'2017'!B69,'2016'!B69,'2015'!B69,'2014'!B69,'2013'!B69)
B17B17=MIN('2021'!B70,'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)
F17F17=SUM('2021'!C37,'2020'!C48,'2019'!C33,'2018'!C32,'2017'!C28,'2016'!C24,'2015'!C26,'2014'!C22,'2013'!C28)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,280
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top