Inventory Automation
Hi There, Some background: We perform Asset Management functions for our department which means working from a particular excel database, manually copying and pasting information from there into an inventory list, printing out that list and then checking the items, coming back to the office and updating the database again which is very time consuming. If the whole process could be automated: from populating the inventory sheet from the excel database, electronically checking and updating the inventory sheet rather than printing it and doing it manually, and then saving; printing and emailing the inventory sheet via push of a button for records keeping purposes to finally updating the database again via the inventory sheet again. Would really appreciate some assistance in this regard. 🙂
Tracking inventory - raw materials
Hello, I am not sure if what I want to create is possible but any assistance or guidance in the right direction will be very much appreciated. I am trying to create a spreadsheet that will allow me to track the aluminum and drywall needed in the fabrication of certain frames that we make. The aluminum comes in bars that are 157.48 inches long. The drywall comes in sheets that are 4ft x 8ft. What I am trying to achieve: Two orders comes in, for example: One is for 8 units that are 18"x48" and the other is for 4 units 12"x12". I have a row for each order. In the "Quantity" column I would input 8 and 4 respectively. In the "Horizontal Side" column, I would input 18" and 12" respectively and in the "Vertical Side" column, I would input 18" and 12". Now the fun starts: I would like excel to somehow be able to calculate exactly how much aluminum and how much drywall I need to manufacture those frames. Meaning, the system should "know" that to make the 8 18x48 frames I will need 8 bars of aluminum: - 2 bars are used for the 18" (18*4=144" out of the 157.48" bar and I am left with 2 pieces of scrap that are 13.48" long) - 6 bars are used for the 48" (15 pieces of 48*3=144" out of the 157.48" bar, and I am left with 5 pieces of scrap that are 13.48" long plus 1 more 48" piece out of the 6th bar and I am left with a bar 109.48" long). Then, the formula should capture that there are now 7 pieces that are 13.48" long plus one more piece that is 109.48" long so, when the next order gets calculated, it uses up those pieces of aluminum (if they are big enough) before it starts using up a new aluminum bar. So, for order #2, I would require a total of 16 12" pieces. The formula would recognize that there are 7 pieces that are 13.48" long that can be used to make 7 of the 16 12" frames, then realize that the remaining 9 pieces can be made out of the 109.48" bar. Same idea with the drywall piece... the formula should calculate how many of the 8 18"*48" panels can be cut out of the 4ft x 8ft sheets and recognize that the 2 12"x12" may be cut out of the remaining pieces. I have the formula that calculates how many bars are needed to manufacture the frames: =IF([@Quantity]="","",ROUNDUP(([@Quantity]*[@[Inside Panel Horizontal]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Horizontal]],0)*[@[Inside Panel Horizontal]],0)),0)+ROUNDUP(([@Quantity]*[@[Inside Panel Vertical]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Vertical]],0)*[@[Inside Panel Vertical]],0)),0) I have not been able to figure out how to do the rest and I am coming to you for help... Does any of you believe this formula/macro is possible? Thank you again in advance!! Marta