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

#### erino

##### New Member
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
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:

### Excel Facts

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

#### Dave Patton

##### Well-known Member
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?

#### erino

##### New Member
@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!!!!

alz

#### Dave Patton

##### Well-known Member
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

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.

alz

#### Dave Patton

##### Well-known Member
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.

Thank you!

Replies
0
Views
93
Replies
4
Views
312
Replies
3
Views
61
Replies
0
Views
45
Replies
10
Views
153

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.

### Which adblocker are you using?

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

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