Forum Discussion
Szymeqpl_
Oct 16, 2023Copper Contributor
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?
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.
Or, if sheet names in column B are in the same order as they are in workbook, that's just
=VSTACK('A:B'!B1:B15)
where A is the first sheet and B is the last one
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 ) )
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.