All right thanks so much for joining me this week this was a highly requested training so i'm really happy to be able to get it to you and i'm going to walk you through every step but first what is a build materials and why do we need it well a bill of materials also known as bom is a basically a list or a compilation of materials and sometimes labor that make up a single component so for example in our sample here we've got a computer set in this computer setup made up of different.
Parts including a motherboard graphics computer so it's made up of all different kinds of parts if you're a computer store you're going to buy all these parts individually you may build them together and sell the computer as a single unit so what you want to do it's very important you want to know how many computers you need to build and it all depends on how many components you have now that assembly is the list of basically all the components that you need so you have an item list and then you what you want to do is you add so many items for example this computer contains one motherboard a computer fan a graphics card and so on and so forth and it also has two ddr so you.
Can put how many are required what you want to do if you've got all your inventory items you want to be able to build them build those and so what you want to do is if you know based on a certain limit you've got 10 available to build so you can build let's say five of those and now you have five that are available to be sold in this so when you go to general info we're gonna see that we now have ten available we just added we had five before now we've got ten available so the idea is to take your materials your raw materials and make up a complete component or a complete product and it can also include labor and that you can then sell that part so you can combine it you have a combined cost and then you have it combined it can be very very complex.
But we're going to break it down super simple and i'm going to show you how you need this this type bill of materials is used in almost every type of industry that kind of sells whether it's manufacturing whether it's hardware whether we're looking at different types of sales or or inventory you use these and you combine these in fact you can even use these in construction my former industry for example if we have let's say we have a bathroom vanity set right so this is a set you've got a faucet you've got supply lines and you want to create that assembly so.
You can sell that to your customers so you would take a bathroom vanity uh some silicone caulk a countertop a faucet and combine and even some labor and labor and then combine all that into a single unit that you can sell and so you can see all the components here so what we're going to do is we're going to use excel and we're going to break it down into some really easy to understand items and also we've got a really really cool general info so when we select individual parts we see we have individual parts of these so each one of these is a faucet and then what we want to do is we want an assembly or make that up so that's what we're going to go over today and i'm going to show you how to do this you can put this in any type of application so i'm going to walk you step by step through the entire process all right so let's get started but before we do that i just.
I've got an incredible bill of materials but i may add on things like invoicing and purchasing onto this and that's available in our patreon account so we take your suggestions your ideas and i add to each application every week and that includes an update application and it also includes an updated training whether it's you know anywhere from 10 to 30 or 40 minutes or whatever it is based on your suggestions ideas all of that is available in patreon the link is down below excel for freelancers you can find us on patreon and that's going to really help us out if just a few dollars a month it'll get you a ton more value so i really appreciate you supporting us on patreon.
All right so let's get started on this training we'll go over the foundation how i created it every component every function every feature every formula every conditional formatting that we're using and how it all does and every of course line of code so we're gonna get into that right now so the basic idea is just a pretty basic form that we have for our materials now these can be any type of material notice that we also have an item name here we've got an item type now if we select just a basic component let's just say silicone caulking white this is an item type and notice how the tab.
The assembly tab is gone right this is not an assembly part however if we click on an assembly now an assembly is also known as a kit it's also known as a group also another bill of materials so it really depends on how you want to call it but there's many names for it but basically it is simply a collection of items and possibly labor that go into a single part that you can all buy and sometimes sell as well so however when we do have a assembly like as in this full computer we.
See that this is an assembly type right so we have different item a service type and assembly type we've got different types based on that and an assembly has this tab available so when we click on this tab here we see that we can then add additional information so let's say we don't need this and we don't need this so we've got some additional information here so this assembly items we can then have a list of our items these are all of our items and then we can add to that list and we can say here's the item description how many are required to build it for example if we're building a computer we may have two pieces of ram or for example if we are building let's say.
Something that i'm more familiar with here inside the bathroom let's say if you're into construction field let's pull up that assembly we've got a bathroom kit and which is this bathroom vanity set now this bathroom vanity set is made up of several items you know and then it's also made up we need two supply lines for hot and cold water we need a valve for hot and cold water so each one of these contains a kit and it's also going to retain some labor probably not one hour probably maybe like five hours of labor so we can update that we just click update and it's going to update that it's going to take that total assembly cost now this is all of the item costs that have now where this item costs come from well that comes from our item database let's take a look inside this item.
Database and see what it's getting also known as an item list we've got an item id this is unique
For every item i've got an item name we've got the type that we just went over remember we've got some service some are item and some are assemblies here we've got a bit of bathtub if we want to work on that we've got a part number a unit of measure also known as uom the category which is just something that we can assign purchase description a sales description which would come in handy on the sales description when you're selling would be on the invoice on a purchase description this would be on a purchase order uh what is the normal purchase quantity when you purchase this what is.That quantity that you're purchasing is it one two or three four this is not a this is not a you know so how many are you purchasing and how many are you selling normally right also we want to know the item cost what is the item cost and how do we know it so we've got item cost we've got a sales price this is what's going to go on the individual both in the item parts also known when you're invoiced it's also going to go on the invoice i also want to know what the quantity in stock how many do i have in stock right so when we go ahead and build that bath and vanity set now we have two in stock right so here we have two in stock but what if we want to build more we can build more and i'll show you how we're gonna do that in just a moment also we have a picture.
Associated with this and we have some notes so that's all that's made up of this item list and that is all translated into here so we have the part and serial number purchase description here purchase default purchase this should be general here so we've got the purchase quantity here the default sales quantity and the item cost and this so this particular this vanity cost right we have based on all the parts in stock if we take a look at this we've got a cast iron bathtub right so we cannot build anything but if i remove that we don't use this generally so let's say we remove.
A few items because we're always just putting them out as a sample so let's take a look at this to create this bathroom vanity kit i need a cabinet i need some silicone i need a countertop i need a single handle faucet i need labor i need a ceramic sink i need two supply lines and i need two elbows so i need all that to create this right so here's my quantity in stock if i have all that but i don't have any this is service rate so that wouldn't matter but if i don't have if i've got 46 available how many can i create well i would say i can create up to six why is that because i only.
Have six tubes of silicone caulking if i were to change that in half you know we only use a half tube i can create a lot more but let's just stick with that for now one so that means that six we can create six of those and that's just what i have here available to build six right if i try to assemble seven of those it's gonna tell us no we cannot assemble more than we have right i can't create that assembly i can do it a maximum of six right so i can create six and what that's going to do is it's gonna when i click that it's automatically going to deduct the inventory now we have no more caulking because we've combined that all into a single kit we.
Can now sell it we've put it all together and we've made it available for sale so now we have nothing available to build and we've assembled a total of eight we now have eight of these bathroom cabinets to stick and of course these bathroom candidates here they contain a single cabinet some silicone caulking a countertop a faucet five hours of labor one sink two supply lines and two valves so this is combined and we have in stock on hand eight now let's say we decide we wanna disassemble we wanna take those apart essentially and be able to sell them individually we can do just that now we have total symbol of 8. if we set 10 to disassemble well that's not going to work.
Because obviously we only have 8 but if we want to let's say we want to disassemble 6 we can do just that and what that's going to do is it's going to disassemble those parts it's going to deduct from the inventory now we only have two available but we also have available now more silicon caulking so we can assemble and disassemble these um let's say assemblies or buildable build materials or boms or however you want to say it back and forth and that's really really important in manufacturing in the industries where we can work with these um raw materials to create products that we can sell so we've got all that so we can be able to disassemble i'm going to walk you through every step once we update that product everything gets saved okay so here in the general.
Information we have basically notes so how are we going to do all this well let me walk you step by step through this application we'll get into the admin which is just very basic i've got a picture folder now this is the folder that all of our parts are located and that looks just like here again we've got our computer and bathroom parts two very different industries but i wanted to show you two very different samples how something like this can be used so we've got a bunch of pictures here and that's essentially it i've got some item types here categories which are whatever you want to put it and some units of measure which is just kind of information that's pretty much it basically items and item data's we've been over now assemblies is everything that we've got we've.
Got an assembly id remember that bathroom kit that bathroom that is called the assembly item nine what does that nine come from it is this inside the item database that bathroom vanity set is item id 9. it is an assembly right we're selling them as each and we have in stock we have two in stock right now and so the best way to do that is to simply track them in assemblies so once we load that assembly item in there i need to make sure we load all these items that are associated with and their associated quantities the requested quantity we have five hours of labor.
To supplies so that means when i load that item in here and load that item all i need to do is
Just change it here loading that one in here let's see the bathroom vanity set it's already loaded in we want all those items to load here's those items right so again this is item number nine and i'll show you that in a minute this item id9 it's not displayed here but it is displayed here in the first column located in b3 so we have that so we know that those are individually tracked likewise of course inside our computer kit here our full computer set that we have all those associated.Items here again two rams and one of everything else that's going to get again we have 10 in stock here so we have available to build five why do we have five available to build why is that because look we only have a total of 10 pieces of ram if each kit uses two pieces we only can build five that's why we have the five so it's really really important to keep track of all this stuff and again just like here that's item number 21 and here's all the items that we use so they're all stored in this assembly database and all i need to know is the quantity required i need to know the item row what is this item row we're going to get into nvb basically it's the row that's associated here 36 through 45. those are the rows so when we bring it back into that.
We know what row to place it on 36 245 which is correctly right here 36 to 45 those are the rows i also want to know the database row that's this row the 14 right all the way through 23 that's the database row when i bring this information back in here inside the items if i decide to make a change here i want to make sure i know what row inside the database to update right so that in this setup if i make that update i know that this changed to 2. if i change it back to 1 click update i need to make sure i know what row to update that's row 23 so that row 23 must come in somewhere and.
It must be placed somewhere but it can be placed off the screen in our case we've placed it over here so we know that that row 23 is right here so the assembly database row stored here i'll go through the other things very shortly so keep that in mind that's what we want to do so we've got the assembly now i've got some empty sheets for invoice purchase order invoice database what i'm going to be doing is i'm going to be adding an invoice i'm going to be adding a purchase order but that's going to be 4 exclusively for our patreon customers so it'd be great to have you in patreon right you're going to we're going to show you how to take all this and put it in an invoice how to put it inside a purchase order and then how to track inventory accordingly for that so.
Make sure you get on patreon all right so let's get into it so how do we do all this so we've got to basically run down that's all the sheets it's relatively simple because our databases simply contain an item database here and an assembly database that's it that's all we have so it's very simple but what we want to do is we want to to basically be able to differentiate when we're loading those items in between an assembly item and we want to differentiate between a regular item because there's some changes that are made notice there's no plus sign here there's no tab here so we can through vba and conditional formatting we're going to show you that however when we load a bom of build materials we do want that plus we do want to expand on that.
And i want to be able to show all the items within that so that's agreeing we're going to really use this really cool to vba i'm going to walk you through that okay so the first thing is notice we've got some conditional formatting and that conditional formatting is going to make this here this tab assembly item show or hide based on what is located right here in h5 and that means if h5 here i want this to show up if it's an assembly if it's anything else then i don't want it to show up for example this hourly labor is a service their service is not part of any so we don't want to make it if we were to change it we would it would then show up so how do we change that how do we get that that's through conditional formatting so let's take a look at that inside the home and go into the conditional formatting and then manage rules and i've got.
Just a few rules to show you okay so the first thing what i want to do is i want to track what column the user has selected this in this case is column number four this and let's go back to our kit so we can select both of them because nothing will happen nothing which is what i want nothing's going to happen here only if it is an assembly item so on our computer here that would be an assembly item then so basically i want to either it's we select column four which is column d or we select column six and i want to place that column through vba i want to put it directly inside.
B1 so b1 is going to tell us this is going to be 4 this is going to be 6. notice how b1 changed right so we're going to do conditional formatting based on the value of located what's in b1 so back into conditional formatting and we manage rules and we take a look at this we're going to edit this rule when b1 equals 6 that would be the assembly tab then what do i want to do i want to format it i want to give it a special format and i'm going to give it this fill effects when we look in the fill effects we see it's a darker a little bit lighter blue to a very light blue so it's that reverse and i want to make sure when we have that tab effect that this second color this light blue.
Is exactly the same as the main background color so that it blends in and also what i want to do is two other things i want to make sure that the font is set to bold and i want to make sure that that lower border is missing and that's how we get that blended effect for the tab so that way when we click assemblies you see that lower border is missing it's in bold font and we've given it that lighter look right so if we take a look in the formatting we go into home and we look in the.
Formatting actually let's format those cells if we take a look inside the format they both have the same format they both have a fill effects of basically the reverse notice it's the same colors here same colors but this one is this simply just light to dark light to dark right so what we do is we simply reverse that when they select okay so we gives it that reverse notice it starts out light and then it goes to dark but here it starts out dark and goes to light and missing the border so both of them have that same format as the conditional feminine so when this becomes one we're going to apply that conditional formatting for four so let's go ahead and highlight and back into that conditional formatting again and then manage those rules so when it's six.
But when it's four we're going to do exactly the same thing the only difference is with this one is we're going to apply it to d3 through e3 and why is that because we're using a combined cell right this is the merged cell here between d3 and e3 so we need to apply it to both of them and then also one more when we have a conditional formatting if we edit this rule we see that h5 it does not equal assembly when it does not equal assembly i want to hide it completely f3 i want to hide it here's f3 i don't want to show it if it's not assembly if it's a service or item or anything else i simply want to hide this tab so when we edit that wrong format we see a few things we see it's got the fill we take a look at the font the font is simply hidden we're coloring the font as the same as the background color if we take a look at the border here we.
Also see that the border is missing on the top and the right we don't want it to show so that way those three different rules allow us this tab feature right so when we make a selection it is vba when we make a selection change that applies that and does all the rest of the work for us so that's what we're going to go in now we're going to show you this tab feature so i we understand that the conditional formatting two rows based on the tab you selected and based on the item type so when we select a different item that tab is going to be completely gone when we go into vba when we.
Make a selection on this this selection nothing's going to happen if this is not assembly right we make a selection nothing will happen so we've told vba hey if this is not assembly when user selects f3 nothing happens right so that's what we want we only want to switch that tab when it is actually an assembly type item so then and only then do we want that to fill great so let's get into vba take a look at that selection change that we're going to have so we get that tab effect now to do that all we've done we're keeping certain rows consistent we're going to keep this row for if we see rows one through three are kept consistent but we're going to do is we're going to hide rows so if we unhide these rows and we see what it looks i'm just clicking unhide i know it's off.
The screen so i'm unhiding those rows so basically all we have is a section of rows for our first main our general info and then we have our assembly info right here so all it is is two simple tabs and then i've got a list of vitamins this is going to help us for auto complete so that way when i put in a computer let's put in put in it's going to automatically complete right so i'll show you that in just a moment but uh basically that's what this list is doing right here okay so we understand that so we understand that we're simply just hiding and showing rows based on whatever steps so when we select general info we want to hide rows 32 through 60 when we want.
To click assembly we want to again show rows 33 through 50 or 60. so that's all we're going to and that's what we're going to do inside vba so let's get inside vba and take a look at that and show you exactly how we've done that that'll be in the developers tab if you don't have the developer tab or you don't see it here all you need to do is just click on the file depending upon what version of excel using properties and then customize ribbon just make sure you've got the developers selected you can also get in using alt f11 that's a shortcut to get you inside vba we're going to focus on the selection change so inside i've got several sheets here but we're going to focus primarily on the items sheet that's the one marked items here so and we're focused on.
Selection change if you're wondering how to get to that worksheet and then you select selection change from here and that's going to show up that's going to show up all the selection change so all the events that we're focused on at this moment are based on selection change when user makes a change to the selection of a cell so this first line of code if target count large is greater than two then exits up this helps prints a lot of errors when users make a change to selection change to a lot of cells we don't want anything to happen we want to make sure that we're exiting out of the sub so that's exactly what we do and it prevents errors all right continuing on if the user makes a selection change in either anywhere from d3 through f3 and h value h5 equals assembly right remember we only want this trigger when it's assembly if they make a change on.
Anything here we only want anything to happen when it's assembly right there's no tab feature when it's an item so only when h5 equals assembly then we're going to continue first thing what i want to do is inside b1 i'm going to place that target column target column inside b1 okay so i want to first thing i want to hide everything i'm going to turn off screen updating this allows those to make a lot faster and get less flashing so the first thing i want to do after that is i want to hide a picture button group what is the pick button group well that is this thing right here i want to hide.
The picture button group right here this button group here is a set of pictures allows us to add and hide pictures it's called the picture button group it's a set of shapes so i want to hide that so we don't need that right if we're sweet i want to hide it regardless of the tab the first thing i want to do is i want to hide it because we don't know what they're going to do so we would either show it or show it and then also what i want to do is the assembly group group here is located right here inside here inside the assembly we have two different tabs right here these two different buttons we're calling the assembly group it allows us to assemble or just assemble our products and.
That's called the assembly group so we want to hide those features regardless and then what we're going to do is simply show them based on the tab that was selected continuing down in the macro okay so once those are hide i want in case there's an item picture there may or may not be an item picture if there is we're going to always give it that name called item picture i want to make sure that this picture regardless of what it is is going to give the name item picture we're going to hide that again regardless of that that way you know generally when they're switching tabs i want to hide it and then we'll show it just in case they're on the right tab so also what i want to do is i want to hide all of the rows hiding all of them 4 360 hiding everything then everything else.
Is based on what what tab was selected if the tab column equals 4 remember that 4 that's column d then it's the general info else down here it's the assembly so we're going to do a few things if they've selected the general info the first thing what i want to do is i want to unhide those rows remember 4 through 31 entire row hidden equals false i want to display all those rows i also want to display the picture button group that's that group that we hit up here i want to make sure that that's displayed and then i want to show a picture but only if it exists that item picture also i want to know if h5 equals assembly then i want to know the expand button remember we saw some things.
On the expand remember this thing was given that called expand button i want to show that plus but i only want to show that plus obviously if it's assembly i want to make sure just to make sure although we have to be in assembly almost always but i want to make sure that we only show this when it's assembly so if it's assembly then we're going to show that and then what i want to do is i want to select something else anytime we use selection change especially for that like this i don't want to keep my selection directly on e3 i want to go somewhere else so it's going to go directly to f5 same thing here when we're on assembly i want to change that selection i want to go to e36 so we're changing it based on that so we just want to make sure we select.
Something like a tab feature we want to select another cell so that's just what we've done inside here so that's all we need to do if it's general info next up just a few lines of code if it is an assembly if it is then in the rows 33 through 60 entire row hidden equals false in other words we're displaying all those rows the assembly group those two pictures that disassemble and assemble those two pictures that's grouped together we're going to show that we're going to display that again e36 we're going to select that cell also i want to double check just again make sure that if it's assembly it should always be but just in case i want to show the shrink button i want if.
The shape shrink button visible equals true if it if any of those buttons the show or hide i just want to make sure that they're hidden so if that shrink button what is that shrink button here that is this button right here this one right here this little minus here is called shrink button right if you like these icons and these pictures and all these shapes i've got all those in a folder available you on the resources inside patreon i've packaged everything together in a nice zip file so you can recreate this when you get into patreon okay so that's what this is called here so that's called shrinkbat i want to make sure that this is not shown when i select here i want to make sure that that's also hidden so that's all we do just to make sure okay so also i want the expand button.
We're going to hide that as well okay so that's all and then we're going to turn application screen updating on so that is it that's all we have to do to get that really really great tab feature working for us so it's very very easy on that okay so what else in this screen i want to show you well first of all we need to be able to add a picture very easily and that's relatively simple if we want to add a picture we can add any picture just select it here and it's going to place directly in here we can also clear a picture if i want a clear picture i can do that and we can just simply add the picture back up here right here this bath and vanity clicking okay it's going to add that picture right back into it so we want to make sure to be able to save it and clicking update it's going to save any changes that we made okay we can also delete the item editing.
But let's go into the macros that add this picture and clear the picture they're relatively simple so all we have to do is go into the item macros right here we're going to start out and then we've got some basically some dimensions and variables we're going to go over i want to know the item row right this is going to be the row of which we're going on right we're going to loop through the items i want to know the item column that's going to be really really important because once we save this information we're going to use data mapping i've got a dedicated video on that but basically it is here again i've gone over this several times if you haven't seen my videos but basically f5 if we look at f5 this is data mapping and basically it maps the item name h5 to the counterpart here so notice this is f5 this is our item name and it comes in this column here.
Item name is f5 our type is h5 so it allows us to quickly bring all this data inside our database or it automatically quickly allows us to take this database information and bring it directly inside here remember i do have dedicated videos check our easy form we've got a few different forms so we've got lots of videos on that so i hope that helps all right continuing on so we've got data mapping here so we need to use the column we're going to be looping through these columns column 1 2 all the way to the last column what's that last column we need to know column.
Because that's going to be helpful it's going to be column 15 so we're going to loop through column 15 okay so we need to know that column inside a variable that's why we have it here i need to know the last row of the database i need to know the last result row now this is going to come in handy when we run our advanced filters the last assembly row remember we've also got assembly rows so i'm going to need to know the last row of that if we need to add them or we need to run an advanced filter we need to know maybe we only want to know assemblies for 9. so we're going to put that into an advanced filter and get those results and then bring those results in so we're going to need to know that so we also have the assembly database row for focus on the specific.
Row and the assembly row i also want to know the item id a picture file and the picture folder remember that picture folder is located directly inside our admin here so we located in c4 so we need to put that inside a variable as well okay also what i want to do is i want to know the full picture path that picture path is basically going to be combined that picture folder located inside our admin here along with the actual picture name so the combination of the name of the picture with.
The folder is going to be our full file path when we combine those we get an accurate path so we're going to need that and also the picture path and the result row as long okay well we need that for our picture we're going to need an item file picture as a file dialog and item shape as a shape we'll be going over this so to add an item picture again we're going to put that item picture folder that string variable based on c4 whatever folder is in c4 then what i want to do is i want to make sure that that has a proper directory if it's not please add a picture file path inside the admin.