Lock excel formulas from changing

[Willyb9]

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.

 

[tcampbe1]

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.

 

[ProjEngKLS]

Willyb9:

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.