Aug 26 2022 11:51 AM
Aug 26 2022 11:51 AM
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!
Aug 26 2022 12:09 PM
Aug 26 2022 01:21 PM
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?
Aug 26 2022 01:42 PM
Aug 26 2022 01:52 PM
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.
Aug 30 2022 07:00 AM
@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.
Aug 30 2022 07:50 AM
Aug 30 2022 08:58 AM - edited Aug 30 2022 09:00 AM
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.
Aug 30 2022 09:02 AM
@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.
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.
Aug 30 2022 09:21 AM
Aug 30 2022 09:48 AMSolution
@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.
Aug 30 2022 10:57 AM
@DianezP the same. Version 2208 specifically.
Did the formulas not work in the workbook I attached?
Aug 30 2022 11:01 AM - edited Aug 30 2022 11:16 AM
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).
Aug 30 2022 11:41 AM - edited Aug 30 2022 11:42 AM
This works! Thank you so much! You saved me a bunch of time and headache and errors!