# 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:

• 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: