Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
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
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 17
This sounds like you're using the wrong tool and would be better suited for an Access Database.
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).
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.
Should be pretty straight forward, 4 tables if you want a drop down of transaction types (member fee, donation, etc.).
MemberID links to Debits and Credits, and Debits and Credits TyeId goes to TransactionTypes table.
You could continue to add on depending on usage (Address table linked to MemberId, PhoneNumbers table linked to MemberId, etc.) If you add on addresses or phone numbers do those in separate tables vs. columns in Members for 2 reasons:
>Members can have more than 1 address. If you did Address1 and Address2 columns, you can only store 2 addresses and there may be instances where you need more.
>You can keep historical records without having to delete them or edit them by adding new items and marking the old ones as inactive. Gives you more history and detail for things like audits or following up on payments.
MemberID links to Debits and Credits, and Debits and Credits TyeId goes to TransactionTypes table.
You could continue to add on depending on usage (Address table linked to MemberId, PhoneNumbers table linked to MemberId, etc.) If you add on addresses or phone numbers do those in separate tables vs. columns in Members for 2 reasons:
>Members can have more than 1 address. If you did Address1 and Address2 columns, you can only store 2 addresses and there may be instances where you need more.
>You can keep historical records without having to delete them or edit them by adding new items and marking the old ones as inactive. Gives you more history and detail for things like audits or following up on payments.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: IC3 GS4 Internet Core Competency Global Standard 4… 135 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.