Absolute cell references change

[at'lin]

Good afternoon all

I am looking for a way to reference a cell in relation to the current one, but a reference which will never change. Absolute cell references point to a specific cell, not a specific location, and will continue to do so no matter how many rows/columns you add

ex (Formula in cell A1)

=$B$

If I add cells above B1 (ie push cells down in that column), then A1 will point to $B$2, $B$3, etc. instead of staying with $B$1

How can I prevent this? I tried using the R1C1 reference style, but of course its functionality is the same, albeit with a different look. Obviously, relative references don't work either.

 

[Frank Kabel]

Hi
try
=INDIRECT("B1")

 

[at'lin]

Once again Frank, you've come through. Thanks

I don't want to get picky, but using INDIRECT() creates a different, if small, problem. It can't be copied over long ranges. ie. If I want to apply "=INDIRECT("B#")" for every row # in A, it would have to be retyped for every cell, correct

Chris

 

[Frank Kabel]

Hi Chris
yes but you could use something like
=INDIRECT("B" & ROW())

 

[Peo Sjoblom]

Try

=INDIRECT("B"&ROW(1:1))

copy down



--

Regards,

Peo Sjoblom

Once again Frank, you've come through. Thanks.

I don't want to get picky, but using INDIRECT() creates a different, if

small, problem. It can't be copied over long ranges. ie. If I want to apply
"=INDIRECT("B#")" for every row # in A, it would have to be retyped for
every cell, correct?

 

[at'lin]

Perfect.

 

[upstate_steve]

Hello

Did you get my post re: OFFSET?

In A1:

=OFFSET(B2,-(ROW(B2)-1),-(COLUMN(B2)-2)).

I spent some time figuring this out and I am very proud of it, so
thought I would share, even though you've already found a solution.

For some reason, I have an irrational prejudice against INDIRECT.

Steve Przyborski
Boston, Mass