I have data in sheet one, A1 thru A50, and in another sheet these cells are
referneced. I need to copy as follows:

Sheet 1 A1 to Sheet 2 A1
Sheet 1 A2 to Sheet 2 A10
Sheet 1 A3 to Sheet 2 A20
Sheet 1 A4 to Sheet 2 A30
and so on....

but everytime I try to copy Sheet 2 A10 gets the data from Sheet1 A10 and
not A2. How do I get around this?

Thanks.

P.S. I am in office 2000 and 2003.

Try this:

=INDIRECT("'Sheet 1'!A"&INT(ROW()/10)+1)

Placing this in row 1 references A1, rows 10 - 19 references A2, rows 20-29
references A3, etc.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andrew" wrote:

I have data in sheet one, A1 thru A50, and in another sheet these cells are
referneced. I need to copy as follows:

Sheet 1 A1 to Sheet 2 A1
Sheet 1 A2 to Sheet 2 A10
Sheet 1 A3 to Sheet 2 A20
Sheet 1 A4 to Sheet 2 A30
and so on....

but everytime I try to copy Sheet 2 A10 gets the data from Sheet1 A10 and
not A2. How do I get around this?

Thanks.

P.S. I am in office 2000 and 2003.

G'day Andrew

Something like this should do the trick for you..

Sub Copy_MyCells()

Application.ScreenUpdating = False
Sheets("1").Select
Range("A:1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2").Select
Range("A:1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Sheets("1").Select
Range("A:2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2").Select
Range("A:10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

Etc.......
Etc.......
Etc.......

Application.ScreenUpdating = True

End Sub

HTH

Regards
Mark.

In Sheet2!A1 use the formula
=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),10)=0,INDIRECT( "Sheet1!A"&(ROW()/10)+1),""))or=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),10 )=0,OFFSET(Sheet1!A$1,ROW()/10,0),"")) and copy downIt's interesting that you have 9 rows difference between the first twodestinations, but 10 rows difference between subsequent pairs.--David Biddulph"Andrew" wrote in ...I have data in sheet one, A1 thru A50, and in another sheet these cells are referneced. I need to copy as follows: Sheet 1 A1 to Sheet 2 A1 Sheet 1 A2 to Sheet 2 A10 Sheet 1 A3 to Sheet 2 A20 Sheet 1 A4 to Sheet 2 A30 and so on.... but everytime I try to copy Sheet 2 A10 gets the data from Sheet1 A10 and not A2. How do I get around this? Thanks. P.S. I am in office 2000 and 2003.

Hi Andrew

Try the below formula in Sheet2 A3 and copy down as required...
=IF(MOD(ROW(),10)=3,INDIRECT("Sheet1!A"&(INT(ROW()/10))+3),"")

for 4,14,24 from Sheet1 4,5,6 try...
=IF(MOD(ROW(),10)=4,INDIRECT("Sheet1!A"&(INT(ROW()/10))+4),"")

If this post helps click Yes
---------------
Jacob Skaria


"Andrew" wrote:

First, thanks for everyone's help.

David, your solution seems to be working the best. How would I adjust it
work on odd rows like 3, 13, 23.... I have another place I would like to use
it as well. I have been play around with it but I keep getting errors. This
would be

S1 A3 to S2 A3
S1 A4 to S2 A13
S1 A5 to S2 A23
...



"David Biddulph" wrote:

In Sheet2!A1 use the formula
=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),10)=0,INDIRECT( "Sheet1!A"&(ROW()/10)+1),""))or=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),10 )=0,OFFSET(Sheet1!A$1,ROW()/10,0),"")) and copy downIt's interesting that you have 9 rows difference between the first twodestinations, but 10 rows difference between subsequent pairs.--David Biddulph"Andrew" wrote in ...I have data in sheet one, A1 thru A50, and in another sheet these cells are referneced. I need to copy as follows: Sheet 1 A1 to Sheet 2 A1 Sheet 1 A2 to Sheet 2 A10 Sheet 1 A3 to Sheet 2 A20 Sheet 1 A4 to Sheet 2 A30 and so on.... but everytime I try to copy Sheet 2 A10 gets the data from Sheet1 A10 and not A2. How do I get around this? Thanks. P.S. I am in office 2000 and 2003.