Forum Discussion
Delete cell contents based on the condition of another cell
You need to adopt the VBA approach to achieve this.
The attached file contains a Change_Event Code on Stock Out Sheet Module and as per the code, if you delete the Code in any row, the corresponding Unit will also be deleted.
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Skip If Target.Column = 3 And Target.Row > 2 Then Application.EnableEvents = False If Target = "" Then Cells(Target.Row, "E") = "" End If End If Skip: Application.EnableEvents = True End Sub
I'm fairly sure that something like that is possible. HOWEVER, for the sake of data integrity, wouldn't it make more sense to keep history of codes and units that did exist even after you've deleted (I assume discontinued) an item?
I speak as the former director of the HR/Payroll database for a major corporation. We would have employees resign, retire, be terminated, etc, (to say nothing of move from one job, one location, to another). We kept history of all employees and their positions, past and present, and yet could always produce an instant snapshot of who was where at any given time. It's from a logical point of view not dissimilar from an inventory system.
I also have a brother-in-law who has a small business. I helped him and his partner create their inventory system, SKUs and all.....it stores history as well as current data.
If I were consulting with you--and I'm not, I'm retired and happily just volunteering some time here--but if I were consulting with you I'd seriously recommend keeping the history of SKUs past and present, and dates and quantities of transactions.
By the way, it always helps to post a copy of whatever workbook you're asking about. It helps those of us who want to help know more precisely what you're dealing with. If it's proprietary, consider making a small mockup with false names, but the same layout.
- Hi, thanks for your quick response. I will try and get a mockup posted soon. I understand what you were saying as far as retaining data. However, when I say I delete an entry I'm just referring to me testing my sheet to make sure all formulas are working correctly. So in doing so I've noticed when I delete the value under Code, then the only remaining information on the sheet is under Units. So I then need to manually erase Units but I would like some kind of automatic function here, in case I forget to delete it on my own.
- So I have posted the workbook. So to help with my issue you would go to the Stock Out tab. Under the CODE column enter any number from 1-105, then press tab twice to enter how many units sold, let's say 2. Then you will see the rest of the calculations unfold. Then say you wanted to erase that entry. You would go back under CODE and hit delete. All other information would then be gone, except for the value under the Unit column.
Wow. That's quite an ambitious workbook. If we were sitting down face-to-face I'd want to back up a bit, maybe simplify things. Maybe not. As to the question you came asking about, can we put that aside for the moment? What you might want to consider is just highlighting the columns into which you actually enter data or numbers...highlight them in a different color, so as to differentiate them from the columns where calculations are done.
There was somebody else on this site creating an inventory system. That person had separate pages for each category of item (four or five of them). You have separate pages for different actions. I think I'd combine them, just noting on each row whether it was item IN or item OUT (or whatever other action categories you might have). I created an example for that person of a different approach. Take a look and see if it makes any sense.
