unexpected formula update when referenced cells are moved
unexpected formula update when referenced cells are moved
(OP)
OK, here is the situation (as attached)
I have a set of input data in E2:E7
I have a set of calculated data in F2:F7 (each cell in col F calculated based on data in same row of cell E).
I want to add new data at the beginning of the table. I cut/paste E2:E7 down one cell and add new data into E2. What happens? The calculated data formulas in col F are unexpectedly updated so that they "follow" the original data. This was not desired and now means that each entry calculated item in column F does not correspond to the data on the same row in column E.
Attached is spreadsheet with a few more details and you can try it for yourself.
This happened to me - not only were the formula's updated but some named ranges updated. This is quite unexpected for me.
Any thougths or commesnts?
I have a set of input data in E2:E7
I have a set of calculated data in F2:F7 (each cell in col F calculated based on data in same row of cell E).
I want to add new data at the beginning of the table. I cut/paste E2:E7 down one cell and add new data into E2. What happens? The calculated data formulas in col F are unexpectedly updated so that they "follow" the original data. This was not desired and now means that each entry calculated item in column F does not correspond to the data on the same row in column E.
Attached is spreadsheet with a few more details and you can try it for yourself.
This happened to me - not only were the formula's updated but some named ranges updated. This is quite unexpected for me.
Any thougths or commesnts?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.





RE: unexpected formula update when referenced cells are moved
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: unexpected formula update when referenced cells are moved
thread770-243100: Excel 2007 Automatically changing formulas?!
But it doesn't seem to be the same thing.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: unexpected formula update when referenced cells are moved
In Excel copy-paste is equivalent to copy in 123 and cut-paste is equivalent to move.
Try a copy-paste, then delete the top cell of the original range.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: unexpected formula update when referenced cells are moved
I can understand the perverted "logic" of updating the formula: If excel sees that upon completion of my cut/paste command it has a formula referencing an empty cell, it thinks that's a problem. But I would much much rather have excel jump up and down with an error message than silently and craftily change a formula without my permission.
I guess the bottom line is to be careful whenever you are cutting/pasting to remove data out of a cell that is referenced by a formula or named range (named range changes just like those dependent formulas!)
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: unexpected formula update when referenced cells are moved
I think it's more a case that often when you move a cell with a formula you will want to to refer to the original (absolute) cells, so that's the default behaviour.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: unexpected formula update when referenced cells are moved
I'm moving a cell that is referred to by another cell. The other cell that I didn't touch has it's formula altered.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: unexpected formula update when referenced cells are moved
Still seems reasonable to me, but maybe I'm just used to it.
Certainly it's a trap to be aware of.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: unexpected formula update when referenced cells are moved
If it were as you say...then cells with "relative references" formula's would never update when the referenced cell is moved (i.e. which is what you were wanting to do, that started this whole thread). So you would never be able to insert a row/column etc...because if it used "relative references" as you take them to mean, they wouldn't update. So then you'd end up making all of them "absolute" as you see it (so the spread sheet would correctly update as you insert more things and move things around)...so then what's the point of even having "relative" capability?
The $'s for relative references and absolute references really only come in to play when you copy the cell that contains the formula. It's just how the formula is propagated to the newly pasted in cells.
Really, all formula's looking at another cell...will always follow that cell no matter where it moves when cells/columns/rows are inserted or deleted. I think it is more important that references follow the cell as it moves...rather than saying Column E is never, ever allowed to be moved...why worry about it?
With the behavior as it currently is,
1) If you want all other cell formulas to keep referencing the a cell no matter where you move it...then do [Cut/Paste
2) If you want all other cells to keep referencing this cell address location (like E2)...then do [Copy/Paste.
It really does work well and allows you to "relatively or absolutely" Copy/Paste to create formulas in new cells, or to Cut/Copy/Paste and keep your other cell formulas always looking at correct cell.
So for your original question, either Insert a new Column somewhere before column E, or Copy/Paste Column E
All of you other formula's and Named Cells will still be correctly referencing each other.
Ken
RE: unexpected formula update when referenced cells are moved
Changing the formula of a cell referencing a cell that is cut/pasted is not a common evolution and is a little more subtle to me. It seems excel has a choice and it's not obvious which choice it "should" take. Particularly considering that the "identity" of a cell logically corresponds to its location on the spreadsheet and not the data contained within the cell. At least that is the logic inherent in this spreadsheet and perhaps why it was not so obvious to me that the formula would follow the data rather than the location.
Copy/paste works. Insert new row does not work because it changes the definition of my named range.
Take a look at attached spreadsheet which is the same as previous but I have added named range input data to refer to everything beginning below the heading input data. I have also created a cell "first cell in input data" which makes it easy to see where the beginning of input data is.
Now highlight row 2 and insert a row. Guess what.... My named range InputData now begins at row 3 instead of row 2. But I use the named range "InputData" in a vba routine - now that first row that I added is invisible to my vba program.
That is why I did not insert a row to begin with.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: unexpected formula update when referenced cells are moved
Of course, the drawback is that we shall remember this when we move cells.
The cut/paste option is a workaround, but I already had surprises doing so.
Another way is to write macros. But then you revert the problem: you don't need to update the formula when moving cells, but if you need to add a new column or whatever to your calculation, then everything is messed up until you update your macro.
IMO, the best way is to think carefully of what we want to perform before doing it in Excel and then decide to go with formula or macro depending on our analysis. Just like one would do with a software writing...
Cyril Guichard
Defense Program Manager
Belgium
RE: unexpected formula update when referenced cells are moved
My standard workaround that I've always used for this type of situation would be:
Insert row at row3
copy E2:F2
paste to E3
paste new data into E2
However, in your case, I fail to see why you've defined a named range, yet you don't use it in the formula, i.e., your calc'd data should be InputData*1.5. This gets around the problem altogether.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: unexpected formula update when referenced cells are moved
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: unexpected formula update when referenced cells are moved
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: unexpected formula update when referenced cells are moved
Your example behaved exactly as I would've expected it to. Inserting or Cut/Paste will change the formulas referencing any cells that were moved by the Insert.
I second what IRstuff detailed. The other alternative would be:
-Select Cell E3
-RMB and choose "Shift Cells Down"
--->This will cause only the data in Colmn E, below Row 2 to move.
-Copy Data from E2, Paste to E3
-Paste to E2
Really the rules are to some extent pretty arbitrary (they could've just as easily gone the other way). Just learn the rules and what to expect and go with that, don't try to fight the system...us little guys never stand a chance.