Forum Discussion
Improve Inventory database - Mimic/Link/clone cell text for another cell
DianezP If having the data you're consolidating reside within a table object is a primary requirement, dynamic arrays will not work. PowerQuery can provide an alternate solution. You could use data/refresh all to pull in the part master data, delete any unneeded columns for that tab & "load to: table" for as many tabs you need. This will eliminate the requirement to manually enter new parts on every tab. You can add more columns to each table which will similarly expand with every new part added in the part master.
In the "Data" menu, use "From File" or "From Text/CSV" if the part master resides in a network folder. Or use "From Table/Range" if you want to use Table1 on the database tab as the starting point.
Lastly, if you don't want to press refresh all when new parts are added, the query properties can be set to "Refresh data when opening the file". This can be annoying if you are opening the document several times/day, but it's an option anyway.
It has many of those features already. So, lets say the office starts buying binders from another manufacturer. I have to gonto each sheet with the binder brand name and update it. So that's 3 to 3different places I have to manually change the data. It's not automated. Or if we no longer carry an item. I have to delete it in 5 different places. It's a bit of a hassle and there have been several occasions where one list had old products (e.g. calendars).
Does that make sense?
- not really. I presume the 'database' tab has the 'master list'? So then what are the other tabs? Are some purely queries from the 'database'? For example is 'current inventory' a subset from the 'database' tab of all items with quantity > 0? Are some tabs intended to be 'filled in'? For example is 'supply requests' intended for users to add an item from the 'database' list and request x number be ordered? are they allowed to insert/add items that don't exist in the database? what is the 'Array' tab? I can't suggest that you use a pivot table or FILTER function here or there if I don't understand how/where the different tables come from and how they get used. Does that make sense?
