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!
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Please refer to post #27
It is your choice
a) Array Enter the suggested formula
b) To avoid having to Array Enter the formula,
convert both or just the second by selecting "Sheets" and pressing F9

The advantage is that you do not have Array Enter the formula.
The minor disadvantage is that the formula is longer.
It may be best to convert both the {2013;2014 ...}. You should not have retype them.

I suggested that you have the sheets in ascending order.

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

To move a sheet, right click on sheet name (Tab) at the bottom, then select move or copy. click on a cell and the sheet moves before that sheet.
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Please ensure that you named the range of cells K2:K9 Sheets or a name that you prefer.

XL2BB would have shown the Named Range information if it existed.

Did you try the mini example that I posted on I believe #25?

It can be very useful to start with a simple example.
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
@Dave Patton - I am so grateful for your patience with me . . . honestly I feel like this is all a foreign language to me . . . I think I have it all figured out now. :) I have plugged it all in and it all seems to work!

THANK YOU SO MUCH!!!!
 
  • Like
Reactions: alz

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks for the feedback and glad that it is working for you.
Which version of the formula did you decide to use?
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

I used this one:

=INDEX({2020;2019;2018;2017;2016;2015;2014;2013},MATCH(TRUE,COUNTIF(INDIRECT("'"&{2020;2019;2018;2017;2016;2015;2014;2013}&"'!B60"),B15)>0,0))

I ended up doing the years backwards because I found for some of the categories the same answer came up in multiple times and I wanted to know the most recent year, so if I reversed the order of the years in the sequence it works out that way.

Again, thank you so much! I really appreciate it. I am so excited about how this workbook is going to help me in my marketing and in my goal setting.
 
  • Like
Reactions: alz

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Glad to hear that you are getting the results that you need.

Do not forget the approach for Min, Max, Sum etc. like =MAX(Start:End!J4). Using this approach means you can add a sheet
for a new year and you will not have to change the formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,364
Members
415,969
Latest member
Rey99

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