How to prevent values from changing when relative cells are changed?

[Gogpo2]

How can I stop values in column 1 from changing when column 1 has inputs (e.g. D2) from column 2 which I want to delete (hence erasing the value in D2 and subsequently changing the values in column 1)? See screenshots below for a clearer explanation:

Captura de pantalla 2015-04-10 17.03.09.png

Captura de pantalla 2015-04-10 17.03.20.png

Captura de pantalla 2015-04-10 17.03.35.png

How can I avoid getting "#REF!" and instead just have the original values in column 'C' as seen in the image below (and first image)?

[TMS]

Try using the Delete key on the keyboard, not the Delete icon on the Ribbon, or from the drop down list.


Regards, TMS

[Gogpo2]

Try using the Delete key on the keyboard, not the Delete icon on the Ribbon, or from the drop down list.


Regards, TMS

I'm afraid that doing this only deletes the values in the cells of column 'D', meaning that all the values in column 'C' change to 0.

[TMS]

You have edited your original post ... you said you wanted to remove #REF errors. The answer I offered did just that. If you move the goal posts, you still don't get the results you want.

If you have a formula and delete the contents of the cell(s) it depends on, you will change the result.

[Gogpo2]

You have edited your original post ... you said you wanted to remove #REF errors. The answer I offered did just that. If you move the goal posts, you still don't get the results you want.

If you have a formula and delete the contents of the cell(s) it depends on, you will change the result.

I edited the post for further clarity, essentially what I'm asking is can I keep everything exactly the same as the first image, but with column 'D' ("Marital") completely gone/deleted (without affecting column 'C')?

[TMS]

... can I keep everything exactly the same as the first image, but with column 'D' ("Marital") completely gone/deleted (without affecting column 'C')?

Sounds like the answer to that question is "no" if formulae in column C have a dependency on values in column D

Pictures are pretty but they're pretty useless in interpreting your requirements or proposing a solution.

I edited the post for further clarity

OK, I really don't know how or what you edited, just that it was after a replied. I'm just confused now.

I suggest that you mark this post as solved, perhaps with a comment that it is "closed/unanswered" rather that solved, and start a new thread. In the new thread, add a link back to this one to provide background and provide a sample workbook rather than pictures of your data.

You could add a link from this one to the new one to complete the loop. A new thread will gather interest and input from others. This thread may have dropped off the radar fro a lot of people.

[newdoverman]

Try selecting column C and then COPY and then Paste Special Values (with the column still selected) to maintain the values while getting rid of the formulae that the values depend on.