Cut and Paste weirdness

[Elesario]

Hi,


Just thought I'd share a bit of Excel weirdness that reared its ugly head today at work. It was strange and interesting and the first time I'd ever seen this behaviour; although maybe the more experienced members are already aware of it.


To demonstrate, try setting up a small table of numbers (RAND() or just 1s; anything you want), at the bottom of the table put a total row (auto sum will be fine). Take a glance at the range used by the formula e.g. A1:A10.


Select a cell half way down the table, cut it and paste it to a cell in the same column under the table you created. Take another look at the total row and the range in the formula should be the same, no problem.


Select the cell that is the very last in the range (A10), cut it and paste it to another cell below the table in the same column... you should suddenly get a circular reference error. If you take a look at the total formula again the range will have changed, i.e. if you'd pasted the cell to A15, then it will state A1:A15 in the total row on A11.


It seems that for whatever reason any time you cut the last cell in a range (cell or entire row) and paste or insert it underneath in the same column you get this error, but if you paste above or to either side it has no effect. You also get the same effect horizontally with row based ranges if you paste or insert to the right. This is especially prevalent if you are moving entire rows/columns about using this method.


Discovered this because a macro that was picking and cutting and inserting a few rows of data in a table into a seperate table below the original suddenly had circular references whereas historically it had never had a problem. This was because just recently one of the rows affected had become the bottom row of the range.


Solution is pretty easy, instead of using a cut, you copy, paste, and then delete the original row (or clear the cell), but I thought I'd share this bit of strangeness to warn the unwary.


I've tested this behaviour in Excel 2003 and 2007, and it affects both.

 

[fred]

my 2 cents on this:


first cent

- don't cut and paste the last row

there is nothing wrong with excel. in a table settng excel thinks you are extending the range of the table. that's why you get a recurring error message.


2nd cent

- remove the "table" setting back to normal range.

check your sum() to make sure it stays A1:A10. then when you "copy" and paste on a new row. then there shouldn't be any problem. Every time you CUT a row above a sum() excel sees it as extending.

So you need to know what you really want from Excel, merely copy the same value or extending the sum(). if you want to extend the sum then you should have insert more line within A1:A10.

 

[Luke M]

I think the problem is more of how XL thinks of the function. The range reference of A1:A10 is treated more like "bookends". XL doesn't care what you put in between or take of of the bookends, it just knows it needs to keep track of those two cells.


Now, when you go to Cut, XL treats this as "move the cell". So, taking that idea with the bookend theory, if you move cell A10 to make it become A15, the formula, which again, is just keeping track of those two cells, changes to become

=SUM(A1:A15)


Workarounds:

I like Fred's suggestion, or you can create a blank "buffer" row, and have that be the "bookend".

 

[fred]

Personally I like buffer row mentioned in Luke's message. That said, many readers/end users would type over the buffer row. Then move the total row down by adding new rows right on the total row, thereby purposefully and accidentally omit the rows sum() should have covered. They drive me nuts to go over all the formulae and check to see if anything have been changed.