Incorporating the Max function results in to an offset function across multiple spreadsheets

[tennisbuff]

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. 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). Any suggestions on this? Thanks so much for your help!

 

[Aladin Akyurek]

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. 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). Any suggestions on this? Thanks so much for your help!

Sheet1, where we want the processing.

Create a range, say in A, housing all of the relevant sheet names, i.e., ONE, TWO, ...,THIRTYONE. Select this range and name the selection as SheetList.

D2, just enter:

=MAX(SUBTOTAL(4,INDIRECT("'"&SheetList&"'!D20")))

D3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDIRECT("'"&INDEX(SheetList,
  SMALL(IF(SUBTOTAL(4,INDIRECT("'"&SheetList&"'!D20"))=$D$2,
  ROW(INDIRECT("1:"&COUNTA(SheetList)))),ROWS($D$3:D3)))&"'!P20"),"")

 

[tennisbuff]

Sheet1, where we want the processing.

Create a range, say in A, housing all of the relevant sheet names, i.e., ONE, TWO, ...,THIRTYONE. Select this range and name the selection as SheetList.

D2, just enter:

=MAX(SUBTOTAL(4,INDIRECT("'"&SheetList&"'!D20")))

D3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDIRECT("'"&INDEX(SheetList,
  SMALL(IF(SUBTOTAL(4,INDIRECT("'"&SheetList&"'!D20"))=$D$2,
  ROW(INDIRECT("1:"&COUNTA(SheetList)))),ROWS($D$3:D3)))&"'!P20"),"")

I ended up doing very close to what you recommended from a suggestion on another forum. I used name manager and created =RIGHT(GET.[FONT=inherit !important][FONT=inherit !important]WORKBOOK[/FONT][/FONT](1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) then used the array formula =SUM(IFERROR((N(OFFSET(INDIRECT(WS_Names&"!D20"),,,,))=C19)*(N(OFFSET(INDIRECT(WS_Names&"!P20"),,,,))),""))

Thank you so much for taking time to respond. I appreciate it!

 

[Aladin Akyurek]

I ended up doing very close to what you recommended from a suggestion on another forum. I used name manager and created =RIGHT(GET.[FONT=inherit !important][FONT=inherit !important]WORKBOOK[/FONT][/FONT](1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) then used the array formula =SUM(IFERROR((N(OFFSET(INDIRECT(WS_Names&"!D20"),,,,))=C19)*(N(OFFSET(INDIRECT(WS_Names&"!P20"),,,,))),""))

Thank you so much for taking time to respond. I appreciate it!

Great, but SUM? Were you not asking for the MAX value and the value(s) associated with?

 

[tennisbuff]

I wanted the result from the MAX formula that is in cell D20 (in the respective sheet) to return the result in cell P20 from the same sheet. It works this way using this formula (so far)l.

Great, but SUM? Were you not asking for the MAX value and the value(s) associated with?

 

[Aladin Akyurek]

I wanted the result from the MAX formula that is in cell D20 (in the respective sheet) to return the result in cell P20 from the same sheet. It works this way using this formula (so far)l.

Suppose we have just ONE, TWO, THREE, and FOUR.

ONE: D20 = 20, P20 = Dan
TWO: D20 = 30, P20 = Jon
THREE: D20 = 30, P20 = Kaan
FOUR: D20 = 15, P20 = Damon

I'd expect a list of consisting Jon and Kaan.

Did I miss something?

 

[tennisbuff]

Suppose we have just ONE, TWO, THREE, and FOUR.

ONE: D20 = 20, P20 = Dan
TWO: D20 = 30, P20 = Jon
THREE: D20 = 30, P20 = Kaan
FOUR: D20 = 15, P20 = Damon

I'd expect a list of consisting Jon and Kaan.

Did I miss something?

What I want is to look at the MAX value of each of the sheets for Cell D20. So in your example that would be sheet TWO and SHEET Three. I think that's where the formula I used could break down because I think it assume that there would be only one value that is a Max (and that is likely based on what we're calculating as they go in the decimals). In your example that would be correct you'd get a Jon and Kaan. I'm going to take your suggestion and try this in the spreadsheet and see if this is better. Actually in my "real" sheet I'm finding MAX; MIN as well as the second and third best and second and third worst along with MIN and MAX. So you're suggestion may be more viable and I'm going to see how that shakes out. Thanks so much!

 

[Aladin Akyurek]

What I want is to look at the MAX value of each of the sheets for Cell D20. So in your example that would be sheet TWO and SHEET Three. I think that's where the formula I used could break down because I think it assume that there would be only one value that is a Max (and that is likely based on what we're calculating as they go in the decimals). In your example that would be correct you'd get a Jon and Kaan. I'm going to take your suggestion and try this in the spreadsheet and see if this is better. Actually in my "real" sheet I'm finding MAX; MIN as well as the second and third best and second and third worst along with MIN and MAX. So you're suggestion may be more viable and I'm going to see how that shakes out. Thanks so much!

You are welcome.

Anyway, the set up I proposed yields a Top 1 list and includes all instances of the MAX value.

Soory for the typo: It should be "consisting of Jon and Kaan", not "of consisting".