How to Keep Formula While Adding New Lines to Database

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

https://docs.glideapps.com/all/reference/using-sheets/functions/arrayformula

Might be useful

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

I just requested access to your Sheet. My email is ariesarsenal@gmail.com.

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.