Paste a value into a referenced cell

I've been using excel for years but have not found a way to solve this issue which seems like it should be simple (or unnecessary even).
If you have 3 columns of data (A, B, C) such as dollars.
Now say column D is a formula of =A+B+C
If the values in row 23 are: A23=1, B23=20, C23=10 then D23=31
My expectation if that if I cut and paste a value e.g. 22 into B23 that D23 will simply update to 42, just as if I typed in that cell (or even copy-pasted into the cell).
What actually happens is D23 shows a #REF! error (formula now changed to =A23+#REF!+C23, as does every cell below it.
Even fixing D23 by copying the formula back into it doesn't fix the ones below it.

It seems to me that it's a fairly normal thing to want to change the values in a cell that is being referenced. Cut-paste is a valid use case here as moving data around is normal too.
The work-around is to copy-paste then go and delete the original cell. This seems to be an unnecessary extra step.

Anyone have a faster way to tell excel that cut-paste a value means I want to paste that value into the cell vs replace the cell itself?

the only way i know how is to use the indirect formula.
=INDIRECT("A1")+INDIRECT("B1")+INDIRECT("C1")
this can get complicated as you would need to adjust the formula so that you are able to drag and drop it down without having to update it manually.

Thanks - I'll take a look at this as a work-around.

Originally Posted by dosydos

the only way i know how is to use the indirect formula.
=INDIRECT("A1")+INDIRECT("B1")+INDIRECT("C1")
this can get complicated as you would need to adjust the formula so that you are able to drag and drop it down without having to update it manually.

Thanks - I'll take a look at this as a work-around.

Hi Sambo Kid - the actual formula isn't really A+B+C, that was just a simplified example. I'll try a few variants though - just wasn't thinking the solution was to change the formula; thought it would be something related to the paste action itself, similar to paste values vs formula but instead paste content...