Copy and paste

[Peter1973]

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.

 

[dafan]

If you use

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

=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.

 

[Peter1973]

I have used the first formula yet this returns the #REF! not sure why.

 

[dafan]

Are you sure your second sheet is called Sheet2? You'll have to edit that name to fit your 2nd sheet exactly.

 

[schielrn]

It works fine for me? How exactly are you entering the formula and into what cells?

 

[Peter1973]

Thanks got it the sheet name had a space