Collaborate on Excel Invoice for Engineering with Ease Using airSlate SignNow

Excel invoice for Engineering

hello this is a very small video I'm going to make about how to manage Bill of Materials in excel sheet so how I did that I was I was trying to buy something somewhere in a YouTube and I never found it so then the some working hard on it I I came up with this idea and I found this so no that's why I'm sharing it that someone someone else can get anything out of it ok so what is it is so here if I there's a situation for the Bill of material and if I have a main assembly that and the sub assemblies of this main assemblies are that assembly a is a part of that main assembly assembly B is a part of this main assembly part 1 and part 2 are the part of that pain assembly so they these first four parts are the main assemblies parts and rest of these are hair that would be the parts of these sub assemblies okay so how I can find out actually if I'm looking for one main assembly what would be the right numbers to order for all of its sub assemblies with respect of their quantities in their sub assemblies so that was the main target and that's what we are going to solve here okay so now what I did actually I added this link column in there from that column number I can actually identify that which are the parts of what so see that if my main assembly has its own serial number unique serial ID it's a 1 and then I have a for pars on the type which is the Bill of material of that main assembly so what I will do I will add the main assemblies serial number in the link column for these fours so now if I will filter the link for one actually I can find what are the parts it belongs to the main assembly so this is how I can get the part of these so now if my assembly a which is a part of a main assembly and its sender number 2 what I'll do I will list down the parts as well in that main list for the folders s as I showed here the sub assembly of C set assembly which this name is C of a that this is a sub assembly of a so and there are more that's sub assembly D of a and the part 3 of a so these three pars these three these the group of these trees are belongs to the assembly a so what I will do I will add a handler of assembly to with these two parts so I can find out okay if my disassembly is belongs to somebody too and if I want to know about what are the parts of the assembly a what I can do I can I can find out okay what serial number and I will filter that with the two and I will get these three parts that these are belongs to recently a so that's how the sequence will continue then every part of the assembly would be in a state column with this unique ID number and in the relation which which parts are belongs to what we will add a handler are linked in in the column and here we can actually play and go back to the okay so now if my bond quantity of main assembly is one and it's telling me that for in this main assembly I would use for time SM da I've used two times assembly B and two parts and one part of party so now if that would remain one so these would be the numbers I'm looking for but assembly a which I'm going to use four times that includes two of sembly c6 enough assembly D and one of part three so but these are not the numbers that I can order actually these are the numbers which would be used for time so I'm looking for not only two I'm looking for five changes just for the quick reference of eight so actually with the one assembly I'm looking for sub assemblies to be ordered for and because that this part is in a bomb quantity it's one but it's that assembly total is being used four times so I need these parts of all times so that's what the kind of then this could be gone into five to six steps that sub assembly observe assembly of some assembly of surface and all the seekers will work well with this with this system so the first column is that you will leave at it as it is because this would be your input number you can change the color of it so actually yeah you can find out ok this would be that you are going to add that how many somebody's I need and rest of these will be calculated so here I added simple vlookup formula there so what I did so I said that look for this number and check in this whole table where that number is and pick the value of its total quantity and that total quantity would be multiplied by its bomb quantity and because that would be done in the same color so it would be recalculating it up to the quantity we will reach so like I to see it better so what I'm doing I am doing that lookup start lookup value for this one and look this value from this whole table and and see that where is the column fourth verse the number four the column fourth when you is and multiply by the spawn quality so now he's looking the bridge was the one and one is this main assembly and then it's going up to the column 4 and is finding that yellow number that is someone and now he is populating in this formula value that it would be a one and now one would be multiplied by the bombs quantity which is the four and I will get the result of four and so now if I will of this formula and I will pull this formula down so everything every time it would be checking like see that 24 so 24 it's looking that what's the parent assembly of this so it's two so you would go back to the two and he will say that okay the two's quantity is 4 because of that this number which was calculated by the base of this and this 4 would be multiplied by a small quantity so I will get the right number out of it so the good advantage of this is that if I will change it into that plane assembly's quantity would be 2 so now the first value is changed and that becomes 8 and now if I was I would see that verse the situation is here so actually it's getting the last number from the table which is calculated literally so he will get the they would start calculating and populating and it will come by the end and complete the reserve what you are looking for so because of that to some leaders assembly a is already populated as 8 and now to 6 and we were looking for that quantity that is 8 and that 8 times 6 would be multiplied up to 48 so whatever the tree is whenever the sub-assembly would fall into that sub assemblies and with how many numbers it doesn't matter that level 1 2 3 4 in what I was trying to use it their formulas were getting longer and longer with this if if part goes to the six levels of of their sub assemblies but in this sequence even you can go for the 20 levels or 50 levels and with no problem with just one click you can get the results that's what I wanted to share this all from thank you for watching