I have a “PRODUCTION” table/sheet that collects data on my sales with a “Collection” column. I created another “Collection” table/sheet to collect the data of the collections that we are receiving. I used the Sumproduct function before to add all Collection from the “Collection” table based on the Billing Number that matches with the Billing Number on my “Production” Table. The formula that I used before is
where $A$2:A is the column of the Billing Number of Collection Table, A79 = the cell of the Billing number in Production, $E$2:E is the column of the Amount of the collection.
The problem is the A79 became constant on the spreadsheet formula.
I tried making the App Formula that I saw on the forums as well and it goes like:
Collection[Net Amount Collected],
([_THISROW].[Billing Number] = [Billing Number]),
The formula works fine, BUT I always need to Edit then save the Row that I added Collection to.
Is there a way to automatically reflect the collected amount without Editing>Saving every item?
Screenshots of my sheets below: