excelexcel-formulaspreadsheet

Formula to grab values from another column and paste with dynamic offset


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?


Solution

  • =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))
    )
    

    enter image description here