Fixed original cell refernce in formula

[MariaExcel355]

Hi,

I have information in Sheet 1 that is being transferred to Sheet 2. I need the formula in Sheet 2 to always refer to the current information inside a specific cell in Sheet 1, not the original information in that cell. For example,

Example 1:
Currently I have this:
Sheet 1:
A1: 1
B1: 2
C1: 3

Sheet 2:
A1: =Sheet1!$A1, therefore A1: 1
B1: =Sheet1!$B1, therefore B1: 2
C1: =Sheet1!$C1, therefore A1: 3

Example 2:
When I copy A1 in the original Sheet to any other location, Sheet 2, will always preserve the information. Therefore:
Sheet 1:
Information originally in A1, moved to B1
Information originally in B1, moved to C1
Information originally in C1, moved to A1
A1: 3
B1: 1
C1: 2

Sheet 2:
Although I've moved around the information in Sheet 1, Sheet 2 remains the same
A1:1
B1:2
C1:3

This is not what I want. I need sheet 2, A1 to always refer to whatever information is inside Sheet 1 cell A1. Therefore if I copied and pasted new information into Sheet 1, A1, that is the new information that would show up in Sheet 2, A1.

Example 3: I want the results from Example 2 above to show up like this on Sheet 2:
Sheet 1:
Information originally in A1, moved to B1
Information originally in B1, moved to C1
Information originally in C1, moved to A1
A1: 3
B1: 1
C1: 2

Sheet 2: Ideal results
A1:3
B1:1
C1:2

The results in Sheet 2 match exactly what's in the original cell assigned to it. I have tried $ and not using $, but that refers to keeping absolute/relative results within the formula. I need the date from Sheet 1 that is in A1 (regardless of what that data may be) to always show up on Sheet 2, A1. I don't need Sheet 2, A1 to always keep the original data that was once in Sheet 1, A1, it needs to show the data that is currently inside Sheet 1, A1 at all times. Whether or not I use a $ doesn't make a difference.

Any help on this would be wonderful.

Thank you, Maria

[npatridge]

This might be a little unconventional, but could something like

=OFFSET(Sheet1!$B1,0,-1)

work in Sheet2!A1?

[Epscan]

Not sure if I understand 100%
But try this
=Sheet1!$A$1

[MariaExcel355]

Thanks for help, but these solutions are not working.
The $ signs don't help.

[Beamernsw]

When you move the cells around, make sure you "move" them and not copy.
Can use ctrl-X and crtl-v to cut and paste or you can grab the cell with your mouse and drag it.
That way if you "move" cell A1 to B1 then your formula on Sheet2 that said =Sheet1!$A1 will automatically change to =Sheet1!$B1

[MariaExcel355]

Thank you but that's exactly what I don't want to happen. If I "move or copy" cell A1 to B1 then I want my formula on Sheet2 to remain =Sheet1!$A1and not to automatically change to =Sheet1!$B1[/QUOTE]

When you move the cells around, make sure you "move" them and not copy.
Can use ctrl-X and crtl-v to cut and paste or you can grab the cell with your mouse and drag it.
That way if you "move" cell A1 to B1 then your formula on Sheet2 that said =Sheet1!$A1 will automatically change to =Sheet1!$B1

[npatridge]

It does matter how you're moving the cells around. If you're using column B to shuffle the cells, then my OFFSET() formula wouldn't work. Alternatively, you could try:

=OFFSET(Sheet1!$F1,0,-5)

to give yourself some room to work.

[Epscan]

Okay another try from me
=INDIRECT("Sheet1!A1")

[Beamernsw]

Sorry Maria, I misread your instructions. Epscan's solution should work perfectly. Very nice work Epscan.