Forum Discussion

Szymeqpl_'s avatar
Szymeqpl_
Copper Contributor
Oct 16, 2023

Modifying the formula.

Good morning,

I would like to receive assistance on my formula. I'm trying to get data from another worksheet that is in B1:B15. I wrote down the formula: =IF(MOD(ROW(),15)=1,VALUE(INDIRECT("'"&B16&"'!B1:B15"))) but every time I drag down I receive(attachment 1 & 2): 

#SPILL!
FALSE

... .

Is there any way to automate this formula or should I use different approach to this problem?

  • Szymeqpl_ 

    Instead of filling down, copy the formula from C16 to C31, C46, etc.

    Or change the formula in C16 to

     

    =VALUE(INDIRECT("'"&OFFSET(B16, -MOD(ROW()-1, 15), 0)&"'!B"&MOD(ROW()-1, 15)+1))

     

    and fill down.

  • Szymeqpl_ 

    In general it could be done as

    with

    =LET(
      sheets, TOCOL(B:B,3),
      first, @TAKE(sheets,1),
      rest,  DROP(sheets,1),
      values, LAMBDA(sheet, VALUE(INDIRECT("'"&sheet&"'!B1:B15"))),
      stack, LAMBDA(a,v,VSTACK(a,values(v)) ),
      REDUCE(values(first), rest, stack )
    )
  • Szymeqpl_ 

    Instead of filling down, copy the formula from C16 to C31, C46, etc.

    Or change the formula in C16 to

     

    =VALUE(INDIRECT("'"&OFFSET(B16, -MOD(ROW()-1, 15), 0)&"'!B"&MOD(ROW()-1, 15)+1))

     

    and fill down.

Resources