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.
You could use the newer dynamic array formulas depending on which version you're using:
The first formula in column L references only 1 table header, while the formula in column N references a range of table headers. When a row is added, this formula will "spill" into new rows and reflect the new row, when a row is deleted, it will shrink.
Note the last item is not being picked up because it's not actually a part of the table. You can either resize or drag the little triangle the arrow is pointing to down to include that row within the table.
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.
- so ARRAYTOTEXT should work. I don't know why it is showing you that text unless you have it formatted as text or something because it should give you an ERROR. ARRAYTOTEXT takes 2 arguments: the array and then the format and you have a 3rd argument AND you are missing the closing ")". That all said I don't know that is the function you really want to use. That function will take the entire column and show it as a list sort of like: (a, a, a, b, b, c, d, e, g). What exactly are you trying to get? maybe you just want to use
=[@[Description]]
to repeat the value on this line under the column 'Description'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.
