Forum Discussion
Formula help
Thanks again
As 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.
As you say, the approaches are broadly the same but, by using array formulas, my approach does not required the helper column, unless you wish to display it. The sum of amounts, for example, can be calculated directly from the data as entered.
- Thanks for that. I have used the conditional formatting to alert people to the change in the sales status and will use your formula.
Thank you