Avatar of sammySeltzer
Flag for United States of America asked on

Excel Sheet

Dear experts,

I have some challenge that i am having difficulty designing.

I have an organization that wtith membership (of course).

Each member is required to pay on an annual basis $120.00

So, the base annual duess $120.00

Beginning from 2014, each year should have two columns, one shows how much each member has paid his/her $120.00 dues.

The second column (again for each year) should show how much is left.

For instance, 2014, member pays $60.00 on one column and the other column for 2014 shows the remaining $60.00 to be paid.

Even though the year begins in 2014, it span up to 2050.

There are also miscileanous events such as fund raising. This could occur at any year.  That even should show how much a member pledged to pay, how much is paid and how much is left to pay.

Eventually, the total paid and the total remaing should show horizontally and then vertically.


John Doe pays $60.00 out of annual dues of $120. Then John Doe pledges to pay $100.00 for fund raising and pays $0.00.

At far right will be a total of $60.00 and total of $160.00 by John Doe.

Then at bottom shows total paid for each year and total owed

Can someone please suggest the best way to design the excel spreadship to accommodate this requirement?

Attached spreadsheet shows sample of what I am trying to accomplish.

Thanks a lot for your assistance.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment

8/22/2022 - Mon
Dustin Saunders

This sounds like you're using the wrong tool and would be better suited for an Access Database.

Thanks for your response.

Any ideas how to get going with Access?

It isn't my strongest suit.
Dustin Saunders

So, you can accomplish something like this with 3 tables and 3 forms.

Members (Id, FirstName, LastName)
Credits (Id, Amount, PostDate, MemberID)
Debits (Id, Amount, PostDate, MemberID)

Multi - Credits
Multi - Debits
Standard - Members w/ subforms for debits and credits.

Now, you can get more complex with totals and such, as well as a different main form that displays on open allowing users to search and work with users but here's a quick put-together to give you an idea (and this was done in about 10 minutes, so if you refine it you can make something functional).  Access is specifically meant to handle this sort of application vs Excel-- and you can create detailed reports for invoicing, etc. off it.

You can expand Credits and Debits to add categories using Lookup fields.  You can add balances to the Members form.  You can lock it down so users can't modify the forms, etc.  

Again, this is just a sample-- if you plan on using Access you might consider doing a 'Front End' Access app and then a 'Back End' for the tables and then using Linked Tables for your users (so you can create/push updates to the UI without having to take down the app).  If I'm being honest, there is a bit of a learning curve from Excel to Access but with a starting point and some time experimenting/googling/asking questions you can assemble an app that works.  Or you can open a gig on EE to get something like that put together that's going to work out of the box (I made this sample in 10 min, an expert with ~2 hrs can do a full featured access app).

If you need help customizing, there are plenty of Access experts who can help if you go that route and you can open questions in that topic.
Your help has saved me hundreds of hours of internet surfing.
Dustin Saunders

Side note-  sorry, so used to just running through questions I don't always check the asker's profile; but considering your certs on EE why not just put together a quick .NET WinForm/SQL app for this vs Access?

You can probably do that way quicker (the only benefit of Access would be if you're handing it off to someone else to maintain who isn't a dev).

Thanks alot Dustin.

I actually thought about doing it in .NET but somehow I thought it could be done in Excel.

I just have to ensure the DB is designed correctly first and .NET would be easy.
Dustin Saunders

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dustin Saunders

You could simply even more and just create a Transactions table-- and from there have a value that indicates if it was a debit or credit.  This was just the easiest way to set it up for Access imo but really if you're doing your own forms and controls you might find that more beneficial.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

Thanks a lot for all your help.

Thank you.

Very helpful.