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

​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 youTI      Read More

​