This is only sort-of Tiller related, but I’m hoping I can turn this into a template if it works to sync information with the main budget/transactions sheet.
I go on vacations with friends pretty frequently where there is a large group that always changes size and participants trip to trip. Usually, a smaller number of the group is planning/paying. We track all of this in a sheet I made (example sheet)
Also tracks who uses what. Everyone in the group doesn’t consume every expense (ex: only some of the group goes on some paid tour)
There is a table at the bottom that is supposed to tabulate who owes who what, factoring in other payments they’ve made (so we don’t move around more money than is necessary)
I had problems making two things work:
I have to manually edit all the names each time - I couldn’t figure out how to get all the formulas to adjust cleanly with any number of people and have the names update in the main table and the calculation table - mostly due to the calculation table
The calculation table barely worked the first time and required me to manually edit every cell. Each cell is a complicated QUERY function and I feel like there’s a better way to do this, but I don’t know what that might be. The query needs to have the column lookup letter (name) change, and it needs to be tweaked slightly for each person and can’t just be dragged. I had another version of this at some point do the difference math but it was so complex to change when the group membership changed and was so error-prone I just got rid of it and everyone do their own math.
Is there a smarter way to do this? I assume so but am stuck on where to start here.
To keep it super simple and transparent, maybe add some additional columns to the right of the original table where you calculate each person’s share of the transaction row. The names can reference the names in the current named columns so you have one master names list rather than re-entering multiple times, and then you do a calculation where if the name is checked, then you take the per person cost applied to that person. Then you have a clean column sum for each person’s trip total and an easy thing to share with everybody so they can have confidence in the math. Then the table below becomes more simple – maybe use the unique function to pull in the list of names you’re dealing with, and then do a sumif to calculate (1) what everybody has already paid, and then pull in maybe via hlookup (2) what everybody owes in total. Anybody who owes money pays it in to a central “banker”, who then uses the collected money to pay out to the people who are owed money.
Were you able to make some of the updates @Caroleen suggested? I modified a copy of your sample sheet with formulas that pretty much follows what was suggested, plus some things I’ve done on similar sheets. See notes in cells that contain formulas for an explanation.
Travel names are referenced from the columns where you check who participated in an expense
To the right, formulas calculate what each person is owed (a green number if they paid for that expense). A red number indicates what they owe the payer.
To the far right, there is a Settle Up section that aggregates each person’s total paid vs borrowed.
I had something developed to share cost with friends as well. The link is here. You can make a copy and try it out: cost share template - Google Sheets
