# Copying formulas and keep sheet reference?

#### bdids

##### New Member
Is there a way to copy a formula that is on Sheet2 that pulls from Sheet1 and when you paste it to Sheet3 its sheet reference changes to Sheet2 automatically?
For example:
The formula on sheet 2 is =Sheet1!B2
When you copy it over to sheet 3 the result is =Sheet1!B2.
I want the result to be =Sheet2!B2

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### RalphA

##### Well-known Member
I entered 10 on Shet1, cell B1
In Sheet2, cell B1, I entered = then went to Sheet1, pressed Enter, and saw, in Sheet2, Cell B1, the value 10, and the formula =Sheet1!B1 in the Formula bar window.
In Sheet3, cell Bq, I entered = then went to Sheet2, pressed Enter, and saw, in Sheet3, Cell B1, the value 10, and the formula =Sheet2!B1 in the Formula bar window.

I believe the above should answer your question.

#### bdids

##### New Member
Thanks for your reply . I understand what you did but I would like the same results as your example when one COPIES the formula from Sheet2 to Sheet3...if possible.

#### RalphA

##### Well-known Member
There are two ways of copying:
1. Go to Sheet1, cell B1, do a Copy, go to Sheet2, cell B1, do a Paste. The visible contents of Sheet1, B1 will be in Sheet2, B1, and the formula, =Sheet1!B1 in the Formula bar window.
2. Go to Sheet2, cell B1, enter = , then go to Sheet1, B1, do a Copy, then Enter. The formula in Sheet2, B1 will be =Sheet1!B1, with the visible contents of Sheet1, B1 visible there. If Sheet1, B1 contained a formula, any pertintnt changes will also occur, such as changing from Sheet1 to Sheet2.

It seemed to me that you asked for the second way of Copy, and, that's what I gave you. If you are not satisfied with that, you will have to post again, saying what you want to be visible in each cell, and what you want to be visible in the Formula bar for each cell. Then, maybe we can get at the answer you seem to be looking for.

Replies
0
Views
574
Replies
7
Views
319
Replies
26
Views
262
Replies
0
Views
485
Replies
1
Views
181

Threads
1,191,399
Messages
5,986,363
Members
440,020
Latest member
IfsandSums

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

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