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.
DexterG_III Hi! Thank you so much for you message. I see in the screenshot that you got the copied text to show up. Is that because the text is not in a table? I can't get a dynamic array to work. The highlighted text is what appears, when I try it. It doesn't show text.
DianezP I don't believe I was able to upload the excel documents in my original reply, fortunately I can now.
I don't believe Dynamic Array formulas can be used within an excel table object. However, they can reference a table object as I've done in the attached (see the tab "CloneFormula"). They can also reference other dynamic arrays, so that the results of additional support or aggregation formulas will grow and shrink as the source data does.
Using something like a part master may be a good place to start. I've used the "Database" tab to pull over product number, description, and the barcode (referred to in the screenshot as Dynamic Array Formula 1). This is the primary formula which will ultimately determine the number of rows being reported on that tab. I then summarized inventory quantities from the Current inventory tab (Formula 2), and the quantity from the supply request tab (Formula 3).
Note the "A3#" reference in the formula below indicates I am referring to the entire dynamic array range. And the CHOOSECOLS function returns the barcode specifically, for aggregating inventory & supply request quantities.
=SUMIFS(Table5[Quantity],Table5[Barcode],CHOOSECOLS(A3#,3))
Note: everything within the thick borders in the screenshot shows what came through originally. It is because the last row on the database tab was not within the table object. If you want new parts added to the database tab to show on other tabs using dynamic arrays, the new parts must be entered within the table object "Table1" in your workbook. I dragged the table handle within the database tab from cell J6 to cell J7 to resize the table & include the orphaned row, and now it is shown below the thick border.
Hope this helps & that the formulas work when you open the workbook I attached here. Let me know of further questions.