Bill of Materials -BOM - Update [GOLD & Silver]
Bill of Materials - Feature, Fix, or Focus
Hello Members!
In this week’s Feature, Fix or Focus, we take last week’s Bill of Materials training and this update includes a brand new Invoice and Purchase order feature that automatically calculates inventories of items and assemblies based on sales and purchases.
I am sure you will really enjoy this. Please don’t forget to add your requests for your own Feature, Fix or Focus each week when I post a new video, right here on Patreon. Each week I will either add a new feature, fix a bug or an issue, or create another training with a special focus on any part, all based on your feedback and comments.
Make sure to view the brand new and updated training below and download the updated workbook in the attachments below.
As always, great to have you here, and thanks so much for your support.
Randy
Comments (12)
I know this is old but hopefully you can answer this. I am trying to format a cell to help calculate total man hours so my items may only be like .10 of an hour and in excel i only see where it will format as time as in 1pm in the day. Is there an easy way to format to calculate mins and hours of labor per item?
Hi and thanks for your message John. One of the formats in the Time Formats options is for total hours. Just go to More Formats > Time then select something like 13:30 This is the one that is used for total hours. I hope this helps and thanks so much.
Hi Randy. Can this be modified to have a dual role as a product configurator? Choose a base product, Add these possible options, with adjustable and maximum quantities? Would be a great feature to add.
Yes Joe, that is a nice idea. Certainly this is fully customizable. I have already completed the update on this one, however you are welcome to download it and customize it however you like. I am sure that in the near future I will be creating another BOM application and will certainly consider your great ideas
Hello Randy! Can time allocation be added to parts or action to show how long it should take to assemble a unit.
Yes certainly. You can add a column to the Item Database called 'Assembly Time' Make sure they are in time format so that when you create your BOM or on a Estimate you can total all of the Assembly time required as a whole. I hope this helps and thanks so mucI hope this helps and thanks so much.
There is no reason why this macro should call on activation of that sheet. The Order_CancelNew is running on a separate sheet which is independent of the (Items sheet) where it calling on range"("D3"). Here is the macro for the Order_CancelNew: NoItems: .Range("B4").Value = False 'Set new Order to false .Shapes("CancelNewBtn").Visible = msoFalse .Shapes("AddNewBtn").Visible = msoCTrue End With End Sub
Can you please share screenshots of the code, sheet, and all of the details in our Excel For Freelancers Facebook Group here: http://bit.ly/groupexcel We have 45,000 members who will be able to help you with this
Hi Randy. I am still learning here and I am still a long way to go. on the BOM (with Invoice & Purchases) on the Order_CancelNew macro, I am having an error when I try to run this macro from the orders sheet. However, the macro runs successfully in VBA. The error message reads "Run error '1004': select method of Range class failed". then it redirects me to the following vba line : Range("D3").Select 'Set to Gen. Info Tab from the following. Private Sub Worksheet_Change(ByVal Target As Range) Dim FoundItem As Range Dim ItemRow As Long Dim ItemNm As String If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("I3")) Is Nothing And Range("I3").Value <> Empty Then Range("D3").Select 'Set to Gen. Info Tab Range("B3").Value = Range("B2").Value 'Item ID Item_Load End If
Usually any error with "Select" means you are trying to select a cell when that sheet is not active. You will have to make sure another sheet is not active when this macro runs or you can always activate that sheet inside the code such as Activate or Sheet1.Activate and put it before the line with "select"
Hi Randy. This is amazing! I would love to have a separate Assemblies_DB to show the date and quantity of assemblies made on a particular date. And I would love to trace the date an assembly was made. Furthermore, I would love to limit the items on the drop down list on the Orders and map them to a particular vender. For example, I have items like soap, toothpaste from one supplier A. So when I select SupplierA I want to show only soap and toothpaste on the drop down list. Another addition I would like to see is that when there is not enough assemblies in stock, then I cannot invoice that assembly or item.
HI and thanks so much. Those are great suggestions and I will try to get something similar in a future training. Feel free to download the application above and customize it however you like. I hope this helps and thanks so much.