Incorporating the results of a MAX function in to an OFFSET Function

I'm pulling my hair out on what I thought would be simple. I have a spreadsheeet with 31 tabs for each day of the month starting with tab ONE and ending with THIRTYONE then the last tab is a "ROLLUP" TAB. In that Tab I want to find the MAX number in all of the Cells D20 across all 31 tabs (no problem using the formula =MAX(ONE:THIRTYONE!D20) ) but want to be able to have another formula to take that result and return the value in P20 of the SAME Sheet that had the Max Value. I thought I could use the offset command and use the MAX function as the reference then just offset the result by 12 columns. I thought this formula would work but doesn't: =OFFSET(MAX(ONE:THIRTYONE!D20)0,12). I've attched a file same. On the rollup tab in Cell D13 is where I'm trying to use the results on the max function from C13. Any suggestions on this? Thanks so much for your help!

Originally Posted by XOR LX

Hi and welcome to the forum!

Go to Name Manager and create a new name, WS_Names, say, and enter this in the Refers to: box:

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

Exit Name Manager. Then, in D13 of your ROLL UP (MONTH) sheet and copy down, this array formula:

=SUM(IFERROR((N(OFFSET(INDIRECT(WS_Names&"!D20"),,,,))=C13)*(N(OFFSET(INDIRECT(WS_Names&"!P20"),,,,))),""))

Note: this assumes that the maximum of each of the Sheets' D20 values is unique.

Regards

I thought I replied but does not appear that it was posted so I'll try again. I wanted to thank you for your reply and help. I followed these instructions and my result came back as "0". I know the Max Value function for cell C13 in the ROLL UP tab is located in the FIVE tab. Cell P20 is 88.12 for that sheet (FIVE) and that's the result I'm looking for in Cell D13 in the ROLL UP tab. Not sure where to go from here, but again thank you for your time and help.

Originally Posted by XOR LX

And you're sure you know how to enter array formulas in Excel? Works fine for me based on your attachment.

Regards

That's probably where I'm having the issue. I do not use array formulas much so my apologies for my lack of experience in this. Here is what I did, but I'm sure something is wrong. I entered in cell D13 of the ROLL UP Tab the following formula {=SUM(IFERROR((N(OFFSET(INDIRECT(WS_Names&"!D20"),,,,))=C13)*(N(OFFSET(INDIRECT(WS_Names&"!P20"),,,,))),""))} (I thought you use the {} in arrays) then used CTRL+SHIFT+ENTER. this is after creating the WS_Names as your instructions noted. What did I do wrong.. as I'm sure my inexperience here isn't helping. Thank you so much for your willingness to help.

Originally Posted by XOR LX

Hi,

I'm a bit worried from your description that you may have entered the curly brackets {} manually (?), which is not what you should be doing (if you commit the formula correctly - with CTRL+SHIFT+ENTER - Excel will insert these automatically).

Regards

I got it working and again want to thank you for your time and expertise. What I did wrong was I copied the array formula with my cursor in the cell I want the result, then did the Ctrl+Shift+Enter and that didn't work. I erased the content, copied in to the formula bar, then did the CTRL+Shift+Enter and that works. This has encouraged me to go back and learn more about array formulas. Again.. thanks so very much for your help!