Forum Discussion
Formula help
NikolinoDE Thank you but not quite right My sheeet looks like this.
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.
- 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 againAs NikolinoDE points out, Excel does not allow formulas to alter input data or calculated data in other cells. That means that the input sales amount will always take the value inserted by the user.
What can be done, however, is to reformulate expressions that reference the amount so that they also test the cancellation status. Thus the amounts and SUM of amounts could read
= IF(Status="CANCELLED", 0, Amount) = SUMIFS(Amount, Status,"")respectively.
Something else you can do to move towards your intended goal is to use conditional formatting to alter how the amount is displayed. You could make the cancelled amounts show as zero but I would be reluctant to display a non-zero amount as a zero; sooner or later, you or someone else will get caught out by that. What I would recommend is a greyed-out display of the original number with strikethrough.
- Thank you very much for your help. Both you and NikolinoDE (above) 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 or possibly conditional formatting to highlight sales that need to be deleted. Thanks again for your help much appreciated.
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)
- 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
