You can download the Excel spreadsheet here (5 January 2020)
Save the workbook to anywhere you like.
The Scenario
A group of Friends often go out together. Concerts, holidays etc. One of them may book the tickets on behalf of the others. So they need to keep track of who owes what to whom, for what.
The group may extend to hundreds eventually! The Friends may need to enter their spend from anywhere in the world.
Everyone should be able to see their account at any time. No one is required to manage the spreadsheet. Each Friend will have a copy of this spreadsheet.
The Solution – Instructions for testing
Open the Excel file Friends Expense Account. Allow macros.
Enter your City.
Under Paid by select one of the Friends from the dropdown list.
Under Item enter the description of an expense item. eg. Party Hats. Be creative!
Enter an Amount.
In the Split section below, for each line select the Friend who owes for that expense item and enter the Amount they owe as a minus amount. Include the Friend who paid for it in the split, if they also share the expense.
Note that the Amounts should cancel out to zero. See this in the Balance Check validation cell.
Now click Update.
The Result
The spreadsheet gets a summary of the Current Account as at now. This shows how much each Friend owes (if the balance is minus) and who is owed (if the balance is plus).
Double Click on any Friend name. The spreadsheet gets the Friend’s Current Account. This shows how the balance owed is made up of what items. It also shows which City the Friend who paid for the item logged it from. (This is so that you can identify yourself in this demo)
Double Click on any Item description. The spreadsheet gets the split of the Item, so you can see who paid for it (positive amount), and who owes and how much.
Click the button Audit. The spreadsheet gets a list of all the Items. This will total zero to confirm that debits equal the credits.
Now go to File > Info and see the File Size of this Excel workbook. (notice that it is small!)
To leave Feedback or to Learn: This technique makes a small spreadsheet very powerful. Click Read Comments to see comments left by other participants from around the world.
Thank you for checking out this demo. Share with friends anywhere in the world.
Ongoing
If interested, you can open this same spreadsheet (the one you downloaded now) next week and see how the expense account is going.
(This solution is inspired by Oz du Soleil ‘Christmas Payments 1 and 2′)
January 2020.
Add comment