locked
change excel cell without changing its formula RRS feed

  • Question

  • greetings.
    How I cando to change the value of a cell in excel without losing the formula associated to that cell?

    thx.


    • Edited by Augusto C Wednesday, April 18, 2012 5:00 PM
    Wednesday, April 18, 2012 4:59 PM

Answers

  • I'm afraid that is not possible - if you enter a value in a cell, you overwrite the formula. You could perhaps use the following instead.

    Let's say the cell with the formula is D2. If you want to override the result of the formula, enter the desired value in another cell, say E2.

    In a third cell, for example F2, enter a formula that will return the result of the formula in D2 if E2 is blank, otherwise the value of E2:

    =IF(E2="",D2,E2)

    You can then use F2 instead of D2 in other formulas.


    Regards, Hans Vogelaar

    • Marked as answer by Augusto C Thursday, April 19, 2012 1:42 PM
    Wednesday, April 18, 2012 8:12 PM

All replies

  • Is is feasible to change the formula? If not, you're probably out of luck, but if you explain in more detail what you want to accomplish, somebody may come up with a workaround.


    Regards, Hans Vogelaar

    Wednesday, April 18, 2012 5:06 PM
  • thanksfor answering

    the explanation is this, I have cells which are calculated by formula, but at some point need to modify and put any value in some cells, but doing this, the formula for that cell disappears, and only I have the courage to enter.
    I want the value you enter appears in the cell but the formula still remains.

    The thing I have other cells that are calculated from these other cells that I want to modify.
    And obviously if the calculation in the wake of the entered values, vary the final result.
    I hope I was clear.
    Thank you.

    Wednesday, April 18, 2012 5:20 PM
  • I'm afraid that is not possible - if you enter a value in a cell, you overwrite the formula. You could perhaps use the following instead.

    Let's say the cell with the formula is D2. If you want to override the result of the formula, enter the desired value in another cell, say E2.

    In a third cell, for example F2, enter a formula that will return the result of the formula in D2 if E2 is blank, otherwise the value of E2:

    =IF(E2="",D2,E2)

    You can then use F2 instead of D2 in other formulas.


    Regards, Hans Vogelaar

    • Marked as answer by Augusto C Thursday, April 19, 2012 1:42 PM
    Wednesday, April 18, 2012 8:12 PM
  • thanks a lot.
    Thursday, April 19, 2012 1:42 PM
  • Not only is this possible it's quite simple. Select the cell you want to change. Go to the home ribbon and select the "Format" drop down button. At the bottom of the list select "Format Cells". This brings up the Format Cells Window. In the Number tab there is a list box entitled "Category". The last Category should be "Custom". Once selected, a text box to the right should appear that says "Type:". Type what you want the cell to display here and hit okay. Now on it will say what you want it to say on the spread sheet but it will still reference cells or formulas you originally had in it.... Hope that's what you were talking about...
    Wednesday, April 23, 2014 9:47 PM
  • https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/filters/new
    Sunday, September 16, 2018 3:44 AM