# How to copy every eleventh cell on one sheet to a list on another?

##### New Member
Hi,

First, let me say thank you to L.Howard for giving me advice on how to properly present my excel problem.
I need to copy data from one sheet to another, but the trick is that the arrangement of cells is different in each sheet.
To be more precise, I need the value in cell A4 in sheet 2 to be in cell A1 in sheet 1, cell A15 in sheet 2 to be in cell A2 in sheet 1, cell A26 in sheet 2 to be in cell A3 in sheet 1 and so on. So basically, the difference is always 11. Since I’m dealing with a ton of data, I need formula for this.

Thanks a lot.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### alvin-chung

##### Active Member
Welcome to the forum!
You can try this formula in sheet 2 A1 and copy down till intended rows...
=INDEX(A:A,(ROW(Sheet1!A1)-1)*11+4,1)

##### New Member
Welcome to the forum!
You can try this formula in sheet 2 A1 and copy down till intended rows...
=INDEX(A:A,(ROW(Sheet1!A1)-1)*11+4,1)

Thanks for quick reply, but unfortunatelly formula doesn't work.
Maybe I should mentione that the actual arrangement of rows is O3, O4, O5 and so on (in sheet 1), and E4, E15, E26 and so on (in sheet 2) .
Now, I need the values in sheet 2 to be in sheet 1.

#### alvin-chung

##### Active Member
Could you fill up the following expected row?

 Sheet1!O3 Sheet2!E? Sheet1!O4 Sheet2!E? Sheet1!O5 Sheet2:E? Sheet1!O6 Sheet2:E?

<tbody>
</tbody>

##### New Member
Could you fill up the following expected row?

 Sheet1!O3 Sheet2!E4 Sheet1!O4 Sheet2!E15 Sheet1!O5 Sheet2!E26 Sheet1!O6 Sheet2!E37

<tbody>
</tbody>

Done :wink:

The thing is, value in lets say Sheet2!E26 has to be in Sheet1!O5. Values are in Sheet2 and I need to "transfer" them to Sheet1

Last edited:

#### alvin-chung

##### Active Member
Try O3=INDEX(Sheet2!E:E,(ROW(O3)-3)*11+4,1)

##### New Member
Try O3=INDEX(Sheet2!E:E,(ROW(O3)-3)*11+4,1)

Still not working. I get error report in parts of the formula marked in red.

#### alvin-chung

##### Active Member
It's working on my spreadsheet, probably we're having different regional settings.
Try change all commas to semicolon? i.e. O3=INDEX(Sheet2!E:E;(ROW(O3)-3)*11+4;1)

##### New Member
It's working on my spreadsheet, probably we're having different regional settings.
Try change all commas to semicolon? i.e. O3=INDEX(Sheet2!E:E;(ROW(O3)-3)*11+4;1)

Now it works great.

Thank you a lot.
You are lifesaver

#### alvin-chung

##### Active Member
Thank you for the feedback, you're most welcome

Replies
2
Views
174
Replies
3
Views
487
Replies
0
Views
103
Replies
1
Views
323
Replies
13
Views
264

1,196,017
Messages
6,012,875
Members
441,737
Latest member
bijayche

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back