Forum Discussion
In Excel cut and paste may change the cell range set in a formula
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.