Excel Inventory List (Built to grow as new files are added)

[Jharrold]

Hi!

My goal is pretty simple but it's presenting some huge problems. 

I am trying to build an Inventory list that will keep track of products coming in and going out when it comes to building computers.

So there are 2 types of excel files in this process. The first is a "build sheet" which is used to build a computer. The second is the inventory list which will record what is being used in this build sheet. 

The idea would be that as a specific quantity is chosen for a specific part in that computer build it will be subtracted from the Inventory sheet. So for example, if we have 5 Processors in stock and then we chose a quantity of "1" on the build sheet the Inventory list would reflect this change and would tell us that there are 4 processors left in stock.

The problem I'm having is figuring out how to do this with multiple build sheets. What I envision is an ever-growing number of build sheets. So because of this, the inventory list will need to be affected by each and every build sheet.

I have tried to use power Queries but I'm having trouble getting the data to correctly fill in. Instead of using the same columns from the first file, each new file is adding its own columns. Just fixing this problem would get me one step closer.

Lastly, the build sheets are using drop-down lists to consolidate the different types of each product. For example when choosing a processor. you click a drop-down and then there is an option for I3, I5, I7, and I9. Because of this drop-down, I also need a way for the Inventory list to know which Processor I am referring to when I am picking a quantity. I have considered, tried, and failed using and "IF" function. 

I am not at all stuck with any one way of doing this if you have any suggestions or new ideas other than the current strategy, I am open to it.

Thanks!

[#2]

@Jharrold Could you share a mock example of your 2 sheets and if possible, your desired output (just manually put it in for now so that we could visualize what you want to achieve)?

[#3]

Here are the 4 files I am working with at the moment. The "Test" files are all the same and the "Master" File is the Inventory List.

In the Master File, you will see 3 sheets

The first sheet is subtracting what happens on the third sheet from what happens on the second sheet.

The second sheet is where all the new inventory for these computer builds will be recorded 

and The third sheet is a Power Query that is updating every time a new build sheet is added to a specific folder on my PC. At the moment those build sheets are represented by "Test 1, Test 2 and Test 3"

(There is a fourth sheet in the master file that is being used to populate some of the dropdowns but it is hidden just cause I got tired of looking at it. The same is true for the test files)

Also because of the drop-down list complication, the only working Formula in the "Master" File is the Power Supply as we only use one type.

P.S. I was able to successfully figure out how to get the Power Query to correctly fill in the data when I add a new Build Sheet to the folder.

Thank you for any help you end up giving! 

@hynguyen 

[#4]

Just so you know The Power Query may stop working because the "Test" Files are no longer in a folder.

[#5]

@Jharrold I have just taken a peek into your files and IMHO the design of your files will not work. Your Build sheets may have various types of the same component and the current layout of your Inv. Out does not show this information. Similarly, how are you going to present different types of processors in Tab Inv. In (which does not depend on your Build sheets)?

I think you can consider designing your Tab Inv. In similar to the current Tab In Stock, i.e. list the component by rows such as Processor - i5-  and put the Date of order as separate columns' header and quantity purchased on that date is shown in the same column for each corresponding component, so that horizontal sum would be total inventory in.

Each build's information is then imported into each separate Tab like Build 1, Build 2, etc. with the same design layout as Tab Inv. I guess VBA may be more efficient to copy the info from each build sheet into each new sheet in your Master List.

Once you have set up the consistent layout across all Tabs in your Master List file, it should not be a problem to deduct the sum of all Build Tabs against Tab Inv. In for each types of each component.