Absolute cell references change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Try

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

copy down



--

Regards,

Peo Sjoblom

at'lin said:
Once again Frank, you've come through. Thanks.

I don't want to get picky, but using INDIRECT() creates a different, if
Click to expand...
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?
 
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
 
Back
Top