NEED HELP! I have the following 3 sheets of ...

[Francisco_Marti]

NEED HELP!

I have the following 3 sheets of which Im trying to obtain reports

PROJECTS (sheet) - id - project name

EXPENSES (sheet) - id - project name (ref to PROJECT sheet) - amount - status (request, in process, paid)

SUMMARY EXPENSES (sheet) - id - project name (ref to PROJECT sheet) - Paid expenses

Im using the following formula for the (Paid expenses) column to obtain the total of expenses paid

=SUM(SELECT(Expenses [amount],AND([_THISROW].[project name]=[project name], [Payment Status]=“Paid”)))

this formula works fine at the beginning to provide the total of paid expenses. The problem Im having is that when I change the status of “Status” column in the “Expenses” sheet, from in process to paid; it does not reflect on the “Paid expenses” column of the “Summary expenses” sheet.

any thoughts?

[Aleksi_Alkio]

It sounds that you are using normal column for your calculation. It will recalculate the value only if you open and save the record. Instead of normal column you could use virtual column which will recalculate the sum when you sync the app.

Instead of using an additional table for the calculation, you could sum the value from the Projects table. The summing itself would be more effective if you do that like…

SUM(SELECT([Related Expenses][amount],[Payment Status]=“Paid”)). With this syntax you would not need to read the whole expenses table again.