How do I anchor a cell reference?

[CasaJay]

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

"CasaJay" <[email protected]> wrote in message
news:[email protected]...
> 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!?
>

[CasaJay]

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" wrote:

> 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
>
> "CasaJay" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

You're welcome. Thanks for the feedback!

Biff

"CasaJay" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>> "CasaJay" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!?
>> >
>>
>>
>>