Lock cell reference to cell, not contents

[4thorns]

This seems like it should be simple but maybe it's bed time!
In cell A1 is the formula =B1. Easy enough.
What I would like is for A1 to reference B1 exclusively. In other words "Do not follow the content of B1 around the
sheet when I move it. Is this even possible?

Thanks,
Doug

 

[cowski]

Sounds like you want an absolute reference as opposed to a relative one.
Try:

A1 = $B$1

 

[4thorns]

I have reviewed the 3 different types of reference and tried them all. They seem to only apply to cells
that are copied from the original.(A1) Regardless of what I've tried, if I move the source cell contents the
home cell retains (follows) the source cells contents. Is there a setting to change what the home cell
references?

 

[4thorns]

Hopefully I'm being clear enough. How's this
A1 formula....=B1
Type 1 into B1
Highlight and grab outside of B1 and move it to C1
Highlight A1
Formula now reads =C1
A1 formula should still read =B1

Doug

 

[handleman]

=INDIRECT("B1")

-handleman, CSWP (The new, easy test)

 

[MintJulep]

OFFSET()

I'm not on a PC, so I can't check the syntax.

 

[4thorns]

Thanks handleman!

I changed ("B1") to ("B:B"), dragged copies down so that it works in a list.
You are the man!

Doug

 

[4thorns]

MintJulep,
I tried offset but it looks like it follows the source as the source moves.
Thanks for the reply tho.
Doug

 

[IDS]

I think Indirect is best for what you want, but Index and Offset will work as long as you don't move the reference cell (the top left cell of the range), so with Index or Offset you can insert or delete rows or columns below or to the right of the top left cell, and formulas will still refer to the same row number or column letter.

Also, if you want to move a value, but still leave formulas unchanged, you can copy and paste, then delete the original, rather than move.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[4thorns]

Thanks Guys.
I'm using the indirect function. The problem is that I need to reference different portions of a list (About 200 rows long). I need to fit the output to paper so I'd like to reference 50 of the rows in the list at a time placing them side by side.

Cells A1 thru A50 reference the first 50 entries in the list.
Cells D1 thru D50 reference the next 50

I tried using named ranges but to no avail. Any thoughts on the proper way to do this?

Doug