# Copy--> Paste problem

#### thewiseguy

##### Well-known Member
Hi everyone,

In sheet 2 cell A1, I would like it to equal sheet!A1.

In sheet 2 cell A4, I would like it to equal sheet1!A2.

Is there a way I can make this easy on myself????

TIA.

-m

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Lewiy

##### Well-known Member
Is this going to continue on down the column? i.e.
Sh2 A1 = Sh1 A1
Sh2 A4 = Sh1 A2
Sh2 A7 = Sh1 A3
Sh2 A10 = Sh1 A4
Sh2 A13 = Sh1 A5

#### thewiseguy

##### Well-known Member
Is this going to continue on down the column? i.e.
Sh2 A1 = Sh1 A1
Sh2 A4 = Sh1 A2
Sh2 A7 = Sh1 A3
Sh2 A10 = Sh1 A4
Sh2 A13 = Sh1 A5

yes.

#### Lewiy

##### Well-known Member
In Sheet2 cell A1 use this formula:
Code:
``=INDIRECT("Sheet1!A"&((ROW(A1)-1)*3)+1)``
and copy it down the column.

#### thewiseguy

##### Well-known Member
In Sheet2 cell A1 use this formula:
Code:
``=INDIRECT("Sheet1!A"&((ROW(A1)-1)*3)+1)``
and copy it down the column.

ok now what if sheet2 A1 is really E14 and sheet 1 A1 is really sheet1 A5?

does the formula change?

#### Lewiy

##### Well-known Member
It would depend whether the rows are still 3 apart each time, would it be:
E14=A5
E15=A8
E16=A11
etc?

#### thewiseguy

##### Well-known Member
It would depend whether the rows are still 3 apart each time, would it be:
E14=A5
E15=A8
E16=A11
etc?

essentially it would look like this:

sheet 1 sheet 2

A5 E14 = A5
A6 E17 = A6
A7 E20 = A7
A8 E23 = A8

#### Lewiy

##### Well-known Member
Ok, in Sheet2 E14 use this:
Code:
``=INDIRECT("Sheet1!A"&(ROW(E14)+1)/3)``
then copy it down column E, you will get a #REF error in E15, E16,E18,E19,E21 etc.

Alternatively, just copy and paste that formula into the cells in column E that you want them, i.e. E14,E17,E20,E23.

#### thewiseguy

##### Well-known Member
Ok, in Sheet2 E14 use this:
Code:
``=INDIRECT("Sheet1!A"&(ROW(E14)+1)/3)``
then copy it down column E, you will get a #REF error in E15, E16,E18,E19,E21 etc.

Alternatively, just copy and paste that formula into the cells in column E that you want them, i.e. E14,E17,E20,E23.

ahh that worked perfectly. thank you very much!

Replies
1
Views
168
Replies
4
Views
130
Replies
3
Views
414
Replies
0
Views
985
Replies
3
Views
387

1,191,555
Messages
5,987,260
Members
440,087
Latest member
Ruppert23

### 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