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!

 

[Dave Patton]

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.

 

[Dave Patton]

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]

@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!!!!

 

[Dave Patton]

Thanks for the feedback and glad that it is working for you.
Which version of the formula did you decide to use?

 

[erino]

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.

 

[Dave Patton]

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.

 

[erino]

Thank you!