Formula help

Question

Hi,&nbsp;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 corresponding Sales Value in the other column to turn to 0 otherwise if blank I want it to show the $ sales value. If someone could help me with the right formula.&nbsp;Thank you&nbsp;Eqa

nikolinode · Answer

Eqa33&nbsp;Here is a simple IF formula that makes it possible.&nbsp;=IF(B2="Cancelled","0",B2)Also attached is an example file.&nbsp;I would be happy to know if I could help.&nbsp;NikolinoDEI know I don't know anything (Socrates)&nbsp;&nbsp;&nbsp;

eqa33 · Answer

NikolinoDE&nbsp;Thank you but not quite right My sheeet looks like this.

nikolinode · Answer

Please more details, in which cell is Sales Value?
only see the amount (I don't know whether this is called a sales value or a deposit).
Remember that the same cell with value entry and formula is not possible, if ... then with other options such as VBA code.

Please provide step-by-step information for your project.

eqa33 · Answer

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 · Answer

Eqa33&nbsp;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.&nbsp;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.&nbsp;Thank you for your time and patience&nbsp;NikolinoDEI know I don't know anything (Socrates)&nbsp;

Forum Discussion

Formula help

10 Replies

Resources