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.
The reason the text does not show up is because I'm trying to copy text in a formatted table. If the text was in a cell, that's not part of a table, it would work. It says it somewhere in Microsoft, sorry I can't remember where. I thought is said it in the help section on the program, but it doesn't. Either way, I'm left with this problem. I thought about creating macro to record the steps of copying and pasting text from one worksheet to another, but even that is problematic because there are formulas linking the worksheets and then they get thrown off. It's take a long time to fix an entire inventory system with every update. I thought DexterG_III was using a different formulas that worked out that issue, but I think it's the same type I tried.
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.
This works! Thank you so much! You saved me a bunch of time and headache and errors!