- Delete cell content based on a condition -

New Contributor

Hello, 
is there an easy way to delete the content of one cell based on the content of another cell?

For example, if cell A2 content is > 0 , then I want to delet whatever is written in the cell A1.  

Thank you so much in advance!

6 Replies

@sarafiore 

 

An "easy way"? Probably not without a macro of some sort.

 

Why don't you give a more complete description of what the overall task is, of which that step is just a part. It may be that there is another approach altogether that would accomplish the bigger objective.

@mathetes 

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.

 

 

 

 

 

 

 

 

@sarafiore 

Perhaps, the formula you need in A2 will be as shown below:

Twifoo_0-1612932433776.png

 

@Twifoo 

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 "ORDEREDwhen it is not ordered yet.

 

@sarafiore 

We cannot delete the contents of another cell through formulas. Perhaps, VBA can but it is not my forte.

@sarafiore 

 

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,