Every nth cell into multiple cells

  • Hi all,

    ive searched but can’t find a solution to this. I gave a big dataset that I want to pull the nth cell value from in a column and the use that value to populate a set of 6 formulas in a column, which then repeat down the sheet.


    for example here is the data set:

    __________A_________
    Row 1 | Val 1

    Row 2 | Other val

    Row 3 | Other val

    Row 4 | Other val

    Row 5 | Val 2

    Row 6 | Other val

    Row 7 | Other val

    Row 8. | Other val

    Row 9 | val 3


    And here is the other sheet with what I need to do:


    ____________A________________B__________
    Row 1 | Data1 | = Sheet1Val 1 & A1

    Row 2 | Data2. | = Sheet1Val 2 & A2 Row 3 | Data3. | = Sheet1Val 3 & A3


    in the real sheet I have a lot of data so I need to be able to drag the formula down the table column and for the references to the cells to increment correctly.


    I have already tried variations on


    Code
    1. =OFFSET($B$5,(ROW(A1)*n)-1,0)


    but can’t get it to increment correctly:


    thanks all for your time!

  • Hi all,


    The forum wouldn’t let me edit post so have clarified the data set as to what I want to do.


    ive searched but can’t find a solution to this. I gave a big dataset that I want to pull the nth cell value from in a column and the use that value to populate a set of 6 formulas in a column, which then repeat down the sheet.

    for example here is the data set:

    __________A_________

    Row 1 | Val 1

    Row 2 | Other val

    Row 3 | Other val

    Row 4 | Other val

    Row 5 | Val 2

    Row 6 | Other val

    Row 7 | Other val

    Row 8. | Other val

    Row 9 | val 3


    And here is the other sheet with what I need to do:


    ____________A________________B__________

    Row 1 | Data1 | = Sheet1Val 1 & A1

    Row 2 | Data2. | = Sheet1Val 1 & A2

    Row 3 | Data3. | = Sheet1Val 1 & A3

    Row 4 | Data1 | = Sheet1Val 2 & A4

    Row 5 | Data2 | = Sheet1Val 2 & A5

    Row 6 | Data3. | = Sheet1Val 2 & A6

    Row 7 | Data1. | = Sheet1Val 3 & A7

    Row 8 | Data2. | = Sheet1Val 3 & A8

    Row 9 | Data3. | = Sheet1Val 3 & A9


    in the real sheet I have a lot of data so I need to be able to drag the formula down the table column and for the references to the cells to increment correctly.

    I have already tried variations on


    Code
    1. =OFFSET($B$5,(ROW(A1)*n)-1,0)

    but can’t get it to increment correctly:

    thanks all for your time!