Improve Inventory database - Mimic/Link/clone cell text for another cell

[DianezP]

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!

[#2]

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.

[#3]

@mtarler 

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?

[#4]

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?

[#5]

@DianezP 

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.  

[#6]

@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.

[#7]

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'

[#8]

@mtarler 

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.

[#9]

@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.  

[#10]

I believe what you saw was that array outputs are not allowed in a table object. So a formula can use array inputs in a formatted table but it can NOT output and array. So actually ARRAYTOTEXT() should work fine as the output is just a single string (and I tested it and it worked fine). But a dynamic array formula like =IF([Last Name]="Smith", "Yes", "No") will not work because it will try to output a column of Yes/No values and that is not allowed in a formatted table.

[#11 · Accepted solution]

@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.  

[#12]

What version of Excel are you using? I'm using Excel 365.

[#13]

@DianezP  the same.  Version 2208 specifically.  

Did the formulas not work in the workbook I attached?  

[#14]

Sorry, I missed your message. I was trying out the Power Query method you suggested. I was thinking along those lines. I'm going to go back and check it out.

Edit: Wow! thank you for you response. And I did not know about any of the formulas you used (=Unique and choosecols), I'm sure this will help not just me but other people. 

Now, I am trying power query, because I think it will work. I'm much more familiar with power query as well. Thank you so much for taking the time to help me (and others).

[#15]

This works! Thank you so much! You saved me a bunch of time and headache and errors!