Copying formulas and keep sheet reference?

[bdids]

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

 

[RalphA]

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]

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]

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.