Forum Discussion
- Delete cell content based on a condition -
I'll try to explain it in the simplest way and I've attached a screenshot for better understanding 🙂
The column "Missing Mat" is the sum of the goods bought or sold in a week and it is calculated through a simple +SUM function.
When stocks go negative, the "Status" column tells me that it is necessary to place a new order for goods: when the "Missing Mat" cell goes negative, the "Status" cell displays the wording "TO ORDER".
To remind me that I placed the order, I write an asterisk next to it.
The "Status" column is in fact calculated through the +IF function: +IF(Missing Mat<0; and +IF(there is an asterisk, then print "TO ORDER"; if false print "ORDERED"), so that "ORDERED" appears after I write an asterisk.
------ +IF(C3<0;+IF(B3="*";"TO ORDER";"ORDERED");"") ------
What I need is that when the "Missing Mat" cell returns to 0, because I have received the order, the cell containing the asterisk is automatically deleted...
I would be very very very grateful for any help!
Thanks in advance.
Thank you very much for your help!
I tried with your formula but the problem still persists...when the "Missing Mat" cell is back to 0 (it changes automatically because it is a summation), I need the cell containing the asterisk to be automatically deleted, if not when the "Missing Mat" cell will go negative again, the asterisk remains and gives me "ORDERED" when it is not ordered yet.
We cannot delete the contents of another cell through formulas. Perhaps, VBA can but it is not my forte.
I fully agree with Twifoo's statement. Deleting by formula the content in cell X based on what's in cell Y is not possible. Not by formula. But if what is IN cell X is itself based on a formula, on, say, what's in cell Y, and that content in cell Y changes, then X can change.
You wrote earlier "To remind me that I placed the order, I write an asterisk next to it." Is that the only way that the fact that you've placed an order is noted? That you manually place an asterisk in a given cell?
That strikes me as not a very robust inventory management system. Why not have a record of order placed, date placed, quantity, etc (whatever would make sense to track)?? And have the asterisk appear (if it's even still necessary, given that you'd have the record)...have that asterisk appear by formula IN the cell where the asterisk is desired, and then disappear when the transaction record shows a shipment received, again by the formula.
It also strikes me as illogical/inconsistent that you see no contradiction between entering the asterisk manually, yet wanting it to disappear on its own. Design the spreadsheet so that BOTH actions take place through a formula.
But deeper, it sounds like a system that might need some sort of re-design. Surely that screen shot that you've provided is not the entire sheet. Is it possible for you, without revealing any proprietary information, to attach not an image but the actual spreadsheet so that Twifoo and I can take a look at it? An image sadly doesn't really help understand the full process. And I happen to know that Twifoo has some experience with inventory management spreadsheets, certainly more than I do. I have a lot of experience with transactional databases in other arenas, and would be happy to bring that experience to bear,
