Calculate SUM of column and use in another list's column calculation

Question

Hullo,

My environment is Publishing site in Sharepoint 2010. I have a library named Invoices with the following structure

+-----------+-----------+--------+
|  Invoice  | PO Number | Amount |
+-----------+-----------+--------+
| Invoice 1 |      1234 |     25 |
| Invoice 2 |      1234 |     25 |
| Invoice 3 |      6789 |     50 |
| Invoice 4 |      6789 |     50 |
+-----------+-----------+--------+

I also have a list where PO is looked up from

+-----------+-------+----------+
| PO Number | Total | Reminder |
+-----------+-------+----------+
|      1234 |   100 | ?        |
|      6789 |   200 | ?        |
+-----------+-------+----------+

To calculate the 'Reminder' I need to sum the 'Amount' column in the Invoices library for each PO and then subtract from Total. i.e it needs to be something like

Reminder = Total - (SUM of Amount where PO Number=1234 from Invoices)

I'm not sure how to enable this. Calculated column doesn't seem to work. Please help me achieve this.

Answer 1

I'd probably use a workflow on your first list to populate a field in your second list. So as invoices are added, the workflow would update a recieved field in the second list. Then in that second list, you can do a calculated colum Total - Received to get your remainder.