Forum Discussion

Eqa33's avatar
Eqa33
Brass Contributor
Nov 01, 2021

Formula help

Hi,   I have a list of sales in a column and another column that shows if a sale has been cancelled, in this column there are only 2 options Blank and Cancelled. When I choose cancelled I want the ...
Formulas and Functions
Eqa33's avatar
Eqa33
Brass Contributor
Nov 02, 2021
Thanks for the reply. The column with Dollar valye is the sales column the other column is blank with a drop down box where the only option is "cancelled". What I was wanting to do, was that upon choosing CANCELLED the coresponding sales value will turn to 0, This is so that the running total will reflect the fact that this sale is no longer happening and therefore sales total needs to be reduced.
Thanks again
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 02, 2021

Eqa33 

Allow me please to repeat, you cannot enter data and insert formulas in the same cell or column at the same time !!

If a formula is in the cell or column then you can only see it as a result or use the values for other cells or columns.

If you then insert a value, the formula in the cell or column will be deleted!

You may be able to do this with VBA but not with a formula.

You must not overwrite the formula itself with the manual value; otherwise, it will be gone / disappeared / dissolved.

 

Nevertheless, I have included a small alternative solution proposal with 3 visible columns and one hidden.

You can make the word CANCELED appear in the "I" column with the right mouse button (right mouse button again and the cell is empty) or you can select it with the drop-down menu.

Look the file with the example please, it might help you.

 

Thank you for your time and patience

 

NikolinoDE

I know I don't know anything (Socrates)

 

  • Eqa33's avatar
    Eqa33
    Brass Contributor
    Nov 02, 2021
    Thank you very much for your help. Both you and Peter (below) are proposing similar solutions i.e. add another column and change the sales value with a formula within that. I'll do a version of that possibly conditional formatting to highlight sale that need to be deleted. Thanks again

Resources