Copy and paste

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933
I have a cell Sheet1A1 which equals sheet2A1, in Sheet1A2 i want this to equal Sheet2 A10.

This trend will then continue i am trying to find a way to copy and paste this down, the interval is always 10 ie
Sheet1 equals Sheet2
A1 A1
A2 A10
A3 A20
A4 A30

Is ther an easy way to do this as the sheet is over 4000 lines so don't really want to do it individual.

ANy help appreciated.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
If you use
Code:
=INDIRECT("Sheet2!"&ADDRESS((ROW(A2)-1)*10,1))
youll get the desired output,

You can only start this on A2 as A1 won't give Sheet2!A1 as a result. You could workaround this with an IF-statement:

Code:
=IF(ROW(A1)=1,INDIRECT("Sheet2!"&ADDRESS(1,1)),INDIRECT("Sheet2!"&ADDRESS((ROW(A1)-1)*10,1)))
But thats a bit of a hassle.

You can just drag these formulas down as they will adjust to 10-20-30 etc.
 

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
Are you sure your second sheet is called Sheet2? You'll have to edit that name to fit your 2nd sheet exactly.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
It works fine for me? How exactly are you entering the formula and into what cells?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

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
Top