Contact US

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.

Students Click Here

Lock excel formulas from changing

Lock excel formulas from changing

Lock excel formulas from changing

I need a way to lock excel formulas from changing when I either delete or move the cells the formulas are referencing.  Using the absolute reference ($ sign) does not lock the formula from changing when the cell it references changes.  It will keep is constant if you copy or move the formula.

There my be other solutions that I am open to.  The problem I am trying to solve is I want to keep a rolling window of about 100 columns on one sheet and draw summary data on another sheet. So what I intend to do with each update is delete a column from the end and insert a new one at the beginnning keeping the formulas on in place on the summary sheet.  

Thanks for your help.

RE: Lock excel formulas from changing

What I've done is do the summary in a different workbook, with linked data, that is closed when I update the table.  Close the table book and open the sumary book.  The links will still be intact.

RE: Lock excel formulas from changing


The following looks to work to me:

1)  Don't move the source data - as you observed the formulas will update.

2)  Copy the entire range of source data.  Paste the entire range of source data (1) column to the right.

3)  Enter your new data in the first column.

4)  Delete the last column of data.

This should work.  The formulas will remain intact and unchanged, your old data will roll to the left and new data will be inserted.

Hope this helps.

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! Already a Member? Login


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