Forum Discussion
Solved
Improve Inventory database - Mimic/Link/clone cell text for another cell
Hi All,
I have been looking for ways to improve my inventory database for work. I am posting this question here because the database is currently in Excel. I have looked at Power Apps, Power BI, Power Pivot and SharePoint, and all of them require code and/or don't have the functionality. However, Excel, for some reason, seems to be missing a super helpful formula! I was hoping to move this to SharePoint.
I have a sample database attached. The items in the database are copied on multiple sheets. Whenever, an item changes, I have to manually change it in every single place it's listed. Is there a way to clone these cells? I tried Arraytotext, valuetotext, =(cell #) and =SUM(cell #) and none of them work. One formula only works if the cells are not in a table (what good does that do?)
1) Clone/automatically update text in all cells from the "Parent cell", if you will.
2) Is there a better way to do this? This is more of a challenge than of question. How would you make this database differently without code and only excel formulas)
Thanks for viewing!
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.
- I read you post and looked at your attachment and still don't know exactly what you want. In general I recommend you have a main 'database' then you can have either pivot table or use the new functions like FILTER and SORT to populate things like 'current inventory'. For a sheet like 'supply requests' you can have a drop-down based on the 'database' for the part names/numbers and then they can enter requested quantities. When the supplies come in someone would still need to 'remove the request' and update the supply quantities in the database.
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?
