Forum Discussion
Excel Inventory List (Built to grow as new files are added)
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)?
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!
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.
- Just so you know The Power Query may stop working because the "Test" Files are no longer in a folder.
