locking excel formulas

0
votes

I have several sheets of data that have formulas written to compare values between the sheets. These sheets are comparing current data from today to the previous data from the day before. Daily I have to move the data from the "current" sheet into the "previous" sheet and then update the "current" sheet with todays today. When I cut the data out of the "current" sheet in paste it into the "previous" sheet it will change the formulas by autmatically updating the sheet name within them, which I don't want it to do. I want the formulas to stay locked and not change when data is moved around.

The work around for this is to copy the data over instead of cutting it, but I want to see if there is any other solution for this.

Here is an example of the formulas I am using

=IF(ISNA(MATCH('Info - Current'!B2,'Diff - Previous'!B:B,0)),FALSE,TRUE)

Then after I physically cut the data out of the "Info - Current" sheet into the "Info - Previous" sheet the formula will change to the example below. Even though the column that the formula is in resides in the "Info- Current" sheet.

=IF(ISNA(MATCH('Info - Previous'!B2,'Diff - Previous'!B:B,0)),FALSE,TRUE)
1
excelexcel-2007excel-formula
Are you using code to do this, or physically selecting Edit > Cut? - LittleBobbyTables - Au Revoir
if you don't want the data to change, do you need to preserve the formulas, or could you paste the values alone? - datatoo
Did you try just pasting the values, or is that not what you are looking to do? - jrad
depending upon the formulas, if they refer to ranges on the first sheet, and you want to preserve those, you might be able to use defined named ranges in the formulas to overcome that - datatoo
can you post a sample workbook? Might be easier for us to offer a solution that fits your needs with sample data. Or another thought, 1) how about each day, copying the previous day sheet and changing to today's date. Won't that keep everything in tack historically, and you just update for the current day? - Scott Holtzman

1 Answers

2
votes

When you cut and paste formulas, Excel updates the pasted formulas to still point to the original cell references in the formulas. This is because you are moving some of the data. The moved data is still tied directly to the originally referenced cells.

When you copy and paste the formulas, Excel assumes you mean to duplicate formulas relative to the cells that the formulas reference. In other words, it doesn't alter the formulas to keep them linked to the original cells that the formulas referenced.

You have already found the best solution, which is to copy and paste to the new workbook, and then delete the data in the old workbook.

References:

http://www.worldstart.com/cut-vs-copy-in-ms-excel/