Forum Discussion
Looking for best practices or advice setting up product stocking workbook
Hi everyone,
I'm looking for advice best practices to build pivot tables and charts for reports on an inventory tracking Workbook. I built worksheets to track food, beverage, condiments, etc for 7 floors (red, green) and 6 (blue) departments we support.
The red and green worksheets have 40 columns of inventory items and the blue floors each have 20 columns of inventory items. There is a separate worksheet that I built that updates the price per unit when I update the prices monthly.
I built a master table using append but it requires each spreadsheet have 65 matching columns multiplied by 365 days each. I'll clean up the empty rows but my master appended table created a clunky 9500 row table. I can't link the price sheet automatically and can't create reports that include cost info. I can get it to update my master append table using refresh. I've have built pivot tables and charts that also function. I can't use the queries to build financial reports.
I also tried separating the worksheets into separate workbooks and used the data model but didn't succeed. I tried importing a file containing the worksheet without success.
Any advice from the community as to which features in Excel might help me create a more efficient way than having 65 column x 9500 row appended table please? It shouldn't need to be 65 columns each worksheet. Next year the table will double to a 17000 row table. Next, how does the data model or power queries ink the food items worksheet to my tables? I'm so close but can't get it.
Thank you
TI
First of all, your screenshots indicate that you are using Excel for the Mac. Then you may forget about linking tables in the Data Model, as that feature doesn't exist in Excel for the Mac. Power Query may be of great help, though the look and feel is different from what you find in most on-line resources in case you seek help there. And the lack of IntelliSense in PQ for the the Mac makes it difficult to get started with if you don't already have quite some experience with PQ on the PC.
Regarding best practice for tracking the data you describe, I would say that you should consider scrapping the 'one sheet per source' approach. Create one large table with a limited number of columns. I guess that all you need is Date, Item, Qty, Location where you only enter items that have a quantity for the day and location may be on of the 7 floors or 6 departments. And don't worry about the fact that this table may become 100K rows long. Excel can handle that. From there it shouldn't be too complicated to link item quantities to their current prices (with one of various lookup functions or Power Query) and create reports (pivot tables) by location, or by item, over time or at any point in time.
But perhaps your situation is special and requires a different solution. Hard to tell if you don't share your file (link on OneDrive, Dropbox or similar).
First of all, your screenshots indicate that you are using Excel for the Mac. Then you may forget about linking tables in the Data Model, as that feature doesn't exist in Excel for the Mac. Power Query may be of great help, though the look and feel is different from what you find in most on-line resources in case you seek help there. And the lack of IntelliSense in PQ for the the Mac makes it difficult to get started with if you don't already have quite some experience with PQ on the PC.
Regarding best practice for tracking the data you describe, I would say that you should consider scrapping the 'one sheet per source' approach. Create one large table with a limited number of columns. I guess that all you need is Date, Item, Qty, Location where you only enter items that have a quantity for the day and location may be on of the 7 floors or 6 departments. And don't worry about the fact that this table may become 100K rows long. Excel can handle that. From there it shouldn't be too complicated to link item quantities to their current prices (with one of various lookup functions or Power Query) and create reports (pivot tables) by location, or by item, over time or at any point in time.
But perhaps your situation is special and requires a different solution. Hard to tell if you don't share your file (link on OneDrive, Dropbox or similar).
I am using Excel for Windows on my work compute. The screen shots were done on my mac at home as a visual representation what I'm doing at work (data privacy)Riny_van_Eekelen
Timbo735 Good for you. The recommendation (i.e. my preferred approach) still stands regarding how to structure the data. Power Query is your friend. Can't really judge if the Data Model will add much.
