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
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.
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.
