SOLVED

Delete cell contents based on the condition of another cell

Copper Contributor

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?

 

 

 

 

 

 

12 Replies

@Jlwiv87 

 

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.

@Jlwiv87 

 

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.

 

 

@mathetes I like the more simplified approach you have there. However, I've spent quite a bit of time on my currently workbook, and just seem to be hung up on this one issue. I would like to find a solution to this issue if there is one.

 

This is my first attempt at this, as well as my first official business. Since, you have a lot of experience in this area. Do you think I'm on the right track. Is there any other useful bits of information I should add to this workbook? 

 

This business will be starting off online, until we're able to open up a brick-and-mortar location. I'm Hoping this workbook will serve my needs as the business continues to grow. 

@Jlwiv87 Allow me to address your specific question regarding the STOCK OUT sheet only. I have converted the table to a structured table and added data validation to column C and changed the formulae in this sheet only, so that they use structured table reference. See attached.

 

And perhaps some tips for further development.

1) Do not use merged cells;

2) Use structured tables and structured references where possible;

3) Give meaningful names to these structured tables;

4) Do not add blank rows within or at the bottom of such tables;

5) Use data validation and VLOOKUP (or better XLOOKUP if your Excel version supports it) in all relevant sheets to match product codes with their names, qty and pricing information;

 

 

 

 

 

best response confirmed by Jlwiv87 (Copper Contributor)
Solution

@Jlwiv87 

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

 

 

 

Thanks for your response. I like what you've done to the table, and I will definitely use the tips you suggested as well. However, even though the sheet is a lot nicer now I still have the same problem. Once the Code is deleted the unit still remains, and needs to be manually removed.

After giving it some thought. I suppose it's alright if there's no solution available for this issue. As I do not plan on accepting returns with this business. So there would be no real need to delete any entries at all. This is just a small defect of this sheet that I came across when putting it together. And since I came across it I would like the issue to be resolved. But, I guess it still works nevertheless. The part that gets me though, is that it seems like there would be an easy solution.
That did the trick! Thank you so much for your help. I'm new to dealing with Excel and have heard about VBA, but haven't got around to using it yet. I will have to explore this more. Thank you for your time and effort!

@Jlwiv87 

 

I'm glad a couple of others with their expertise have joined in this thread. Let me just address a couple of the questions you asked me.

This is my first attempt at this, as well as my first official business. Since, you have a lot of experience in this area. Do you think I'm on the right track. Is there any other useful bits of information I should add to this workbook? 

I don't really have a lot of experience in this area. My working career (I'm long retired now) involved a stint as director of the HR/Payroll database for a major corporation. In connection with that I learned a lot about relational databases, the logical process known as "normalization"--which has the goal of eliminating redundancy in data--SQL (Structured Query Language, and the like. My experience with product inventory lists per se is next to zero, the worksheet I shared and another I did in conjunction with my brother-in-law's business.

It's that corporate experience that leads me to want to combine tables that essentially contain the same info, with just a small distinction, using a cell in each row (i.e., a column) to recognize that distinction. So in your case, for example, Opening Stock, Stock In, and Stock Out have many columns in common, despite representing different actions, different points in the process. That overlap (otherwise known as redundancy) raises red flags to my "normalization" mind. It may be appropriate (and it may not be) to combine them into a single table (with a column that represents how each row figures into the flow) and have another page that reflects "Current Stock" that is all calculated, Opening + StockIn - StockOut

 

Other than that, you look like you're on the right track. Take the recommendations of @Riny_van_Eekelen very seriously. It's a common mistake of beginners with Excel to spend more time on jazzing up the appearance of their spreadsheets--colors, combined cells, empty rows--that make it look nicer, cleaner, clearer to humans, but truly interfere with Excel's abilities to manipulate data. Learn to use structured tables.

 

This business will be starting off online, until we're able to open up a brick-and-mortar location. I'm Hoping this workbook will serve my needs as the business continues to grow. 

 

Best wishes to you. Will you be doing this on Shopify/Etsy/Wayfair (or all of them)? Or stand-alone? It's an exciting time, with the technology available today to make it possible to enter into a business and do sales world-wide. Check out https://www.carthage.co  It's my the business my brother in law has launched. Somewhat in the same niche as yours, home goods, but not a competitor.

You're welcome @Jlwiv87! Glad it worked as desired.

That's a beautiful site, very nice. I plan to sell on all of the mentioned platforms including locally, using Offer Up and Craigslist. I guess when you said Payroll, for some reason my mind went to Accounting. So I was just curious if I had enough useful information. So that in the future a CPA could use the information provided and have a clear picture of what's going on in the business.
Trying to Jazz up this Excel sheet is definitely something that I've been doing. And, as someone new to Excel it's taken me quite a bit of time. So I do I need to stop putting in so much time and energy there. The good thing is, I believe that was one of the last things I was hung up on. So now I can focus my efforts elsewhere. Thank you for your good wishes!
1 best response

Accepted Solutions
best response confirmed by Jlwiv87 (Copper Contributor)
Solution

@Jlwiv87 

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

 

 

 

View solution in original post