pull dynamic cell range addres from a cell on another sheet created using a formula and use it in another formula

Question

I am using this formula to create a cell range dynamically:

=concat("'180Day Runners'!A3:A" , COUNTA(A:A)) depending on the number of rows the countA finds the result is '180Day Runners'!A3:A389

I would like to pull this result into another sheet and use it in a formula but I am not able to get the syntax correct in the nested formula.

=arrayformula(transpose(ARRAYFORMULA(SORT(TRANSPOSE(QUERY('Raw Run Data'!A:O,"SELECT F,G, max(J) WHERE G='5K' AND M = 7 AND ( F=1 "& concatenate(ARRAYFORMULA( " OR F=" & **'180Day Runners'!A4:A388** )) &" ) GROUP BY F, G pivot O ",1)),1,false))))

I would like to replace the '180Day Runners'!A4:A388 with the result of the first formula but I am unable to get the syntax correct. I have tried INDIRECT('180Day Runners'!B2) but this just produces a parse error with no explanation.

here is a link to a working copy

https://docs.google.com/spreadsheets/d/12SLmsLOSfCOHt9e1C065LPjrXUP3n1qIwsAZFzphN8U/edit?usp=sharing

Cell C3 is where the main formula is situated on the 5K progress sheet

here is a link to a copy of the sheets docs.google.com/spreadsheets/d/… — Richard Dodd
Cell C3 is where the main formula is on the 5K Progress sheet — Richard Dodd

player0 player0 · Accepted Answer · 2019-11-06T18:04:39

try:

=ARRAYFORMULA(TRANSPOSE(SORT(TRANSPOSE(QUERY('Raw Run Data'!A:O,
 "select F,G,max(J) 
  where G ='5K' 
    and M = 7 
    and F matches '"&TEXTJOIN("|", 1, '180Day Runners'!A4:A)&"|1'  
  group by F,G pivot O", 1)), 1, 0)))

(untested because your sheet can't be copied)

or perhaps:

=ARRAYFORMULA(TRANSPOSE(SORT(TRANSPOSE(QUERY(TO_TEXT('Raw Run Data'!A:O),
 "select Col6,Col7,max(Col10) 
  where Col7 ='5K' 
    and Col13 = '7' 
    and Col6 matches '"&TEXTJOIN("|", 1, '180Day Runners'!A4:A)&"|1'  
  group by Col6,Col7 pivot Col15", 1)), 1, 0)))

pull dynamic cell range addres from a cell on another sheet created using a formula and use it in another formula

2 Answers