INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Lock cell reference to cell, not contents

Lock cell reference to cell, not contents

(OP)
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

RE: Lock cell reference to cell, not contents

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

CODE

A1 = $B$1 

RE: Lock cell reference to cell, not contents

(OP)
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?

RE: Lock cell reference to cell, not contents

(OP)
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

RE: Lock cell reference to cell, not contents

=INDIRECT("B1")

-handleman, CSWP (The new, easy test)

RE: Lock cell reference to cell, not contents

OFFSET()

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

RE: Lock cell reference to cell, not contents

(OP)
Thanks handleman!

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

Doug

RE: Lock cell reference to cell, not contents

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

RE: Lock cell reference to cell, not contents

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/

RE: Lock cell reference to cell, not contents

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close