Reference other tables in formula like worksheets in excel

[Jesse_Martin]

How do I reference a second table in a formula? I need to sum all the values of a column in a second table and then divide it by a rollup of a records associated values in that column.

What I am trying to do:

I have an event where all the people attending are in one table.
I have another table where I have all the receipts people submitted for things they had to pay for and there’s a link between the two. (IE, attendee x paid for receipts A, B and F)

What I want to do for each attendee on the attendee table is sum all the values of the receipts, divide it by the number of attendees and and subtract the total receipts that attendee already paid for. That way I can get an average cost that each person needs to pay for their “share” and automatically subtract what they’ve already paid.

Thanks for any help.

[Chester_McLaugh]

My gut response was that this wouldn’t be possible, but turns out it is totally doable. You’ll need three tables:

1. Attendees
2. Receipts
3. Report

The secret is to link every Attendee to a single record in Report which will allow you to COUNT, SUM, etc ALL attendees. So the only “gotcha” is that every time you add an attendee you’ll need to link them to the “Everyone” record in the Report table.