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.
Example:
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.
C--Users-sokeh-Desktop-APO.xlsx
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.
Example:
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.
C--Users-sokeh-Desktop-APO.xlsx
Dustin Saunders
This sounds like you're using the wrong tool and would be better suited for an Access Database.
ASKER
Thanks for your response.
Any ideas how to get going with Access?
It isn't my strongest suit.
Any ideas how to get going with Access?
It isn't my strongest suit.
So, you can accomplish something like this with 3 tables and 3 forms.
TABLES:
Members (Id, FirstName, LastName)
Credits (Id, Amount, PostDate, MemberID)
Debits (Id, Amount, PostDate, MemberID)
Forms:
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/ask
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.
membercontributions.accdb
TABLES:
Members (Id, FirstName, LastName)
Credits (Id, Amount, PostDate, MemberID)
Debits (Id, Amount, PostDate, MemberID)
Forms:
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/ask
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.
membercontributions.accdb
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).
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).
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Thanks a lot for all your help.
ASKER
Thank you.
Very helpful.
Very helpful.