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

badrat

New Member
Joined
Jan 8, 2014
Messages
6
Hi,

First, let me say thank you to L.Howard for giving me advice on how to properly present my excel problem.
I really need your advice, or better to say, help.
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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)
 
Upvote 0
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.
 
Upvote 0
Could you fill up the following expected row?

Sheet1!O3Sheet2!E?
Sheet1!O4Sheet2!E?
Sheet1!O5Sheet2:E?
Sheet1!O6Sheet2:E?

<tbody>
</tbody>
 
Upvote 0
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:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top