Editing a value that a formula pulls up

You cannot edit a value when the formula is still live, meaning to say that you still can edit the formula and any changes in the cells referenced in the formula will result in this cell total being changed.

What you would probably want to do it copy the whole column, or row, that has the formula and paste it in the next column As Values, which will give you a column with the same values than the one with the formula but this one you can edit the values (and it won't update with changes made in the referenced cells.)

Here's an animation how to do it:

Yisroel Tech

Posted 2017-05-09T00:04:58.943

Reputation: 4 307

Thank you Yisroel for your input. I am trying to make a inventory sheet with the formula. For example, I type in "paper" in a cell, and through series of formula, I was able to generate a list with all the inventory that had the word "paper" in it, and give quantity in inventory accordingly. However, my next challenge is that after it gives a quantity, I want to update it. But since that cell is a formula, I can't edit that cell. Do you know of any other way around this? Such as using =INDIRECT or other functions? Thank you, – Philip – 2017-05-10T01:33:43.233

No. There is no way to edit the value when it is really a (living) formula. The only way you can accomplish that would be by copying the column/row that has the formulas and paste them as values, as I showed in my answer. – Yisroel Tech – 2017-05-10T02:48:45.897

What do you need to do with the value? – Sam – 2017-05-09T05:18:43.327

So it's for a inventory sheet I made. This formula gives me quantity in the inventory I am looking for. So after this formula feeds back quantity in the inventory I'm searching, I want to makes changes to this quantity conveniently without searching through all the inventory, and changing it manually. Thank you – Philip – 2017-05-10T01:36:54.000

You can copy the column of formulas and Paste special values in another column and Edit the cells when finish you can delete the formula column – Sam – 2017-05-10T05:20:24.843

Editing a value that a formula pulls up

Answers