Forum Discussion
In Excel cut and paste may change the cell range set in a formula
I have an Excel sheet where I defined a sum of four cells with SUM($A$2:$D$2):
| A | B | C | D | SUM |
| 1 | 2 | 3 | 4 | 10 |
The issue I noticed is that if user selects cell A2 and then cut it and paste it to another cell within the range, the formula in E2 changes to SUM($B$2:$D$2), i.e. the cell A2 gets omitted from the sum.
The same happens when cutting cell D2 on the other end of the range.
Is there a way to prevent changing the cell range set in a formula after such cut and paste operation?
After some more thinking I found a workaround: in the cell range I added a hidden cell before and after the range of cells where users enter values:
That way, it someone cuts any of the visible cells and pastes it inside the range, the SUM formula won't change as the first and last cells in the range are not affected by the cut&paste.
4 Replies
After some more thinking I found a workaround: in the cell range I added a hidden cell before and after the range of cells where users enter values:
That way, it someone cuts any of the visible cells and pastes it inside the range, the SUM formula won't change as the first and last cells in the range are not affected by the cut&paste.
That's by design!
Don't cut and paste but copy and paste and then empty the cell copied from. Thus copy A2, paste in B3 (for example), select A2, press the delete button on your keyboard.
Your advice do not work in real world because users forget such advice and do whatever they like when you give them a spreadsheet to fill in some values on daily bases.
If your users can remember how to cut a cell and paste it somewhere else, they surely can remember to copy it in stead. Aren't you under-estimating the intelligence of your users? So be it then.
