Forum Discussion
Delete cell contents based on the condition of another cell
Hello, I am creating a inventory management system. The page in question is on my Stock Out page. This page has the following column headings: DATE, SALES ORDER, CODE, NAME, UNITS, VALUE PER UNIT, TOTAL VALUE, SALES PRICE, TAX RATE, TAX COLLECTED, TOTAL SALE, PROFITS.
The area I am focused on is when I put in the code which is basically a SKU, the name pops up. I then enter how many units of that item sold. At that point all of the calculations are performed returning my profit from the transaction.
The inventory management system is for a new business so it's not being 100% used at this point. However, my main focus is on Code and Units. When I delete an entry by removing the value under Code, all other cells get deleted except for Units. Since the sheet is so large, with lots of cells and columns. It can be difficult to realize that the unit column still contains a value from a previous entry. I would like for the Unit cell to be deleted if the Code cell is deleted or already blank. Is this possible?
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
12 Replies
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.
