I have dynamic sum formulae in a certain sheet making critical calculations for my app. However, if I add an entry in the app, the formula is discontinued yet I want the same formulae applied to the data in the new entries
You probably need an Array Formula. There is LOTS of info in this forum. Just do a quick search.
i tried on though, let me check for more topics. thanks
2 Likes
thank you so much
taking a look now
1 Like
Hope this helps.
Thank you so much. I’m learning more about array formulas indeed. Much appreciated
3 Likes
Feel free to let us know if you need help.
Definitely, I do need help.
I have 2 separate sheets; one with lists of products and I want them to be summed up depending on category and project in the second sheet. Hence I’m running a dynamic sumif formula to sum up the products.
Here is a link with an example of the scenario I’m facing:
https://docs.google.com/spreadsheets/d/1XYuuxtlPOgE2h5cwVePRG92urNVr_Qe27Ubxj6Wkc_4/edit?usp=sharing
Thank you so much
Ook, i have just sent you with editing access
If I’m correct, we did talk about the same thing here.
Your structure is weird. I believe a better way to do this is to have your rowIDs in the Panel list as one single column.
Let’s say:
Cabinet ID | Panel ID | Row ID | Quantity
Then in the second sheet have three columns
Row ID | Panel ID | Quantity
Then the quantity column can be derived using a rollup inside Glide on top of a relation, or an easier query inside the Sheet.
1 Like
You’re correct, it’s the same file we talked about the other time…
Im new to database structures but I have been using spreadsheets for a while now for basic data work.
Im working on your advice…
But it would help to explain to you where the Quantities of the panels in the suggested Cabinet ID | Panel ID | Row ID | Quantity sheet are coming from
Each project has many cabinets, each cabinet has many panels…this would mean the sheet you have suggested will be populated by so many panels, cabinets, and projects, etc.
so far in the existing structure, the number of panels per cabinet per project are calculated by an INDEXMATCH from the projects sheet which…
However, I’m going for your suggested restructuring…
I hope I don’t consume to many rows and i figure it out lol
Yes, you’re understanding it right. It’s the only way to make the structure easier to be calculated either with an Arrayformula or a Glide flow. It will definitely consume more rows.
Ok… But the desired outcome at the end of the day is to count the unique panels per project when u open the projects details…
How will that happen if the roll up columns are on a separate sheet? 
Are the “projects” ones with the “rowIDs”? If so, I assume you have a Project Sheet with those IDs, then you can use a relation pointing that column to the Project ID column in the Panels Sheet and show them as an inline list inside the Project details view.
Yes, there are rowIDs in the project sheet, but the cabinets are in the projects table as column headers. Since each project can have more than 1 of each cabinet type the user populates the columns with the number of the cabinets that particular project has…
So it becomes difficult to make a relation
Meanwhile, the current structure is fully functional except for the arrayformula issue lol
I’m studying around the relations and learning more about them in the meantime
Non the less, do you have an example of your suggested structure I can study?
I think probably this would help.
https://docs.glideapps.com/all/guides/deep-dives/design/subcategories
Probably you won’t need to scale this up but generally I don’t think making column headers rowIDs from other Sheets is good practice.
Your formulas would work in the Sheet with the current structure you have, but I would recommend re-building the whole flow to have a clean database for an app in the long term.
1 Like
Thank you…
Going through…
1 Like