I have an Excel Sheet with values in column J starting in row 22. I also have a number in l35, this can change depending on values in other cells. I want to have a formula in column O, starting at cell O22 That does the following, it places 0 values in the first N rows where N is the value in L35, then pastes the values in column J starting from row 22. So if L35 is 5, Column O will have 5 rows with zeros followed by the values in column J. So basically, the value of L35 is the offset for the copying.
I am struggling to do this.
So far I have tried putting this in cell O22 and pasting it downwards
=IF(ROW()-ROW($O$22) < $L$35, 0, INDEX($J$22:$J$100, ROW()-ROW($O$22)-$L$35+1))
Any suggestions?
=IF(ROW() - 21 <= $L$35, 0, INDEX($J$22:$J$1000, ROW() - $L$35 - 21))
A readable and easy to understand version, assuming you have Excel 365:
=LET(
n, $L$35,
values, INDEX($J$22:$J$1000, SEQUENCE(ROWS($J$22:$J$1000) - n, 1)),
result, VSTACK(SEQUENCE(n, 1, 0, 0), values),
TAKE(result, ROWS($J$22:$J$1000))
)
or if you want to stop after x rows:
=LET(
n, $L$35,
x, 50,
stop, MIN(n,x),
values, INDEX($J$22:$J$1000, SEQUENCE(ROWS($J$22:$J$1000) - stop, 1)),
result, VSTACK(SEQUENCE(stop, 1, 0, 0), values),
TAKE(result, ROWS($J$22:$J$1000))
)