Forum Discussion

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 09, 2020

Krista1228 Allow me to get to this from another angle. You have used PowerQuery to combine the three location sheets into a consolidated inventory sheet. Then you have a separate sheet for "Received" and created, more or less, a copy of the consolidated sheet to which you add information on received and sold quantities to arrive at the closing stock.

 

Why not start by creating a table that holds all product specific data (species, core, grain, width and size) and give each product a unique code.

 

Then, collect all your transactional data (i.e. product code, qty, transaction type [e.g. received, sold, return, transfer], location, date, etc.) in one single table. For instance, a transfer would be entered as a minus for the "from-location" and a plus for the "to-location", with the type "Transfer". In the end, all transfers should add up to zero.
Now you can create current stock listings from all sorts of angles, using pivot tables. By location, by product, by size, by species. Whatever. It's totally dynamic and without the need to concatenate a mock-up product names containing all that information information.  No need for VBA code that cuts rows of data from one sheet to paste it into another. Easier to maintain, less prone to error and giving you full detail and history of how product has moved through the organisation over time.
Krista1228's avatar
Krista1228
Copper Contributor
Oct 09, 2020

Riny_van_Eekelen I was thinking about it this AM and came to a very similar thought. Basically make a master list table-that if needed I can add to as we're constantly getting different stock in because its very custom work. I need to have the 3 locations broken up and visualized because I'm not the only person looking at this and most the guys that use it are not Excel knowledgeable or tech savvy. I can easily vlookup or query the product and input it into separate sheet labeled with that location, based on location (which would be my data validation list in location column). Essentially I'd be working backwards from what I currently have done. I would be pulling from a master list and inputting it into the location sheets instead of compiling all data from sheets into one. 

 

This way when something moves- in the master list I can drop it down from lets say CNC Room to Container and when I update the queries etc it would automatically update. 

 

I hope that made sense haha.

 

The concatenation was something I was asked to do by our Financial Advisor as this list will eventually go into quickbooks. We use specific "terms" in quickbooks for each product, usually starting with thickness, size, species, core, then grain/grade. This would make it easier to implement it into quickbooks since the terms are already being used there. 

 

Lastly, I had the received on a separate sheet as well because its easier to have a table on another sheet vlookup the information from received and add it in, vs adding it in manually by looking for it down the very long list. 

 

As I said, I am very new and have been learning by myself as I go. These tables have come a long way since what they looked like a couple months ago (haha). I keep striving to do better and make them more easy to use as I learn more. 

 

I thank you very much for your time and getting back to me 🙂 

 

 

Resources