unexpected formula update when referenced cells are moved 1

[electricpete]

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?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

I will mention in this particular case there was a very good reason for not inserting a row in front of the data (which would have preserved the formula's). I also happened to have named ranges referring to these columns. I know that when you insert data above a named range, the definition changed and I didn't want the starting point of the named range to be altered (wanted it to refer E2:E100 when finished just as it referred to E2:E100 when started). This concern may or may not have been unfounded, but I thought I was doing the "safe" thing by not inserting a row because I didn't want to change the cell address of any formula or named range... just add new data at the beginning of my list and shift everything else down.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

This has a vague resemblance to:
thread770-243100
But it doesn't seem to be the same thing.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

Pete - this sounds like normal behaviour. In the "good old days" of Lotus 123 we had a Copy command (which did not adjust formulas to the new location) and a Move command (which did).

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/

 

[electricpete]

Thanks. That is a good work-around for my particular spreadsheet which has quite a lot of other stuff going on that limits my choices. For example I can't easily copy the entire row (input data and calculated data) because some of the columns have other variations that would be messed up by that evolution.

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.

 

[IDS]

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 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/

 

[electricpete]

There is no formula in the cell I'm moving. If such were the case I know exactly what it would do (relative references move and absolute references stay).

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.

 

[IDS]

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.

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/

 

[brengine]

I also agree that this is the "expected behavior", this is basic functionality of any Spreadsheet I've ever used.

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

 

[electricpete]

I expect formulas to update when I insert row or column and the need for that is obvious. Otherwise I can disrupt a formula without touching either the cell with the formula or the cell that is referenced by the formula. Also it is an evolution that I do all the time.

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.

So for your original question, either Insert a new row 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.

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.

 

[FrenchCAD]

Well, Excel is programmed in such a way that if you move a cell which is referenced elsewhere in a formula, then the formula is automatically updated. That's the way it works. One has to admit it can be kind of useful to avoid updating formulaes each time we make a change on our spreadsheet.

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

 

[IRstuff]

The "following" of relative addressed cells has always been the behavior Excel for as long as I've used it, which is at least 15 yrs.

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

 

[electricpete]

However, in your case, I fail to see why you've defined a named range, yet you don't use it in the formula

This is a large project from which I recreated a very small portion to show the point. There are many columns of input data, each identified as a named range. The project has vba code that reads the input data from those named ranges.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

And if you use the name ranges in the formulas, you could do your updates as desired, without hacks.

TTFN

FAQ731-376

 

[brengine]

electricpete,

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.