How do I anchor a cell reference?

[Guest]

To greatly simplify this, cell A1 says "=B1" and cell A2 says "=A1"
If I move cell B1 to cell C1, cell A1 now says "=C1" and of course, cell
A2=cell A1
Next problem... if i move data into cell B1, cell A1 gives me a #REF!
How can I anchor the reference in cell A1 to always say "=B1"?
How can I move data into a referenced cell without getting a #REF!?

 

[Biff]

Hi!

A couple of ways:

This will always refer to cell B1:

=INDIRECT("B1")

If you entered that in cell A1 and then inserted a new column A you would
then get a circular reference because the formula is now in cell B1 and the
formula refers to cell B1.

This will always refer to the cell to the immediate right of the cell
reference in the formula, in this case, A1:

=OFFSET(A1,,1)

As is, it refers to B1. If you were to insert a new column A then it would
refer to C1.

Biff

 

[Guest]

Hey Biff, that certainly gives me a direction i didn't know about, thanks!
But boy, do i have a lot of work to do on my spreadsheets now.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Hey Biff, that certainly gives me a direction i didn't know about, thanks!
But boy, do i have a lot of work to do on my spreadsheets now.