H
Harry Stevens
To All,
Thanks for the previous information that I have asked for and for that
I have gained from reading the post here. I did not know you could do
so much with Excel.
I have recently taken on the duties as Treasurer for our Homeowners
Association and I am trying to set up a workbook that I can properly use
to show the homeowners and the board of director where the money is
coming from and going to and do some comparisons between years. I
currently have 8 worksheets and I am trying not to have to enter
duplicate information on each sheet so I have used my "Income Stmt"
worksheet as kind of a master. And so far that has work out pretty
well, but each Budget year have some similar expenses and some different.
Here is how my workbook is set up, each worksheet is label and it's
purpose as follows:
Itemized - Checkbook - Records all expenses/deposits - This is a data
validation from a List
Bank Acct - Contains ending balances of our accounts
Income Stmt - This is what I consider my master sheet and pulls data
from the corresponding sheet base on Month and Year entered
Monthly Exp - Takes the inputs from Itemized and broke out by month
using formulas like this -
=SUMIF(Itemized!$D:$D,"="&($A4&TEXT(B$2,"mmm-yy")),Itemized!$C:$C)
Monthly Inc - Takes the inputs from Itemized and broke out by month
using formulas like this -
=SUMIF(Itemized!$D:$D,"="&($A4&TEXT(B$2,"mmm-yy")),Itemized!$C:$C)
Budget06 - These are my budget worksheets that I use to tally the
required maintenance and projects to set the current years association
assessment fee and will continue to add as the years go by.
Budget07
Budget08
My problem is that every year the number of line items in the budget
are different and what I would like to do is depending on the date
entered on the Income Stmt that the corresponding budget replace the
line items on my Income Stmt worksheet. I have named two ranges on each
budget worksheet BudgetYR_Inc and BudgetYR_Exp. I have pasted below two
different budget years and my income stmt. I do not have any like named
ranges on the Income Stmt, but think I could do the same as my Budget
worksheet and have the ranges replaced depending date entered. I do
this for the dollar amounts but have not figured out how to do that for
the line items.
=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A:A"),$B9,INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!D
"))
As you can see spacing is different on each budget and Income Stmt.
Any help with explaining to me or showing me how to accomplish the
sway would be greatly appreciated.
Thanks in Advance
Harry
Budget06 Budget07 Income Stmt
Income Income
100 - Late fees 100 - Late Fees
200 - Interest Income 200 - Interest Income 100 - Late Fees
300 - Misc Income 300 - Misc Income 200 - Interest Income
400 - Violation Income 400 - Violation Income 300 - Misc Income
500 - Gate Key Income 500 - Gate Key Income 400 - Violation Income
600 - Assessments 600 - Assessments 500 - Gate Key Income
610 - Special Asses 610 - Special Asses 600 - Assessments
1180 - Reserve Asses 1180 - Reserve Asses 610 - Special Asses
Total Income Total Income 1180 - Reserve Asses
Expenses Expenses
1140 - Insurance 1140 - Insurance
1180 - Reserve Txfr 1180 - Reserve txfr
5001 - Management Fees 5001 - Management Fees Expenses
5010 - Legal Fees 5010 - Legal Fees 1140 - Insurance
5020 - Accounting Fees & Tax Return 5020 - Accounting Fees & Tax
Return 1180 - Reserve Txfr
5040 - Corporate Annual Report 5040 - Corporate Annual report 5001 -
Management Fees
5060 - Telephone 6000 - Electricity 5010 - Legal Fees
6000 - Electricity 5060 - Telephone 5020 - Accounting Fees & Tax Return
6009 - Repairs and Maintenance 6009 - Repairs & Maintenance 5040 -
Corporate Annual Report
6010 - Ground Maintenance 6009a - Seal Coating 5060 - Telephone
6014 - Gate Repairs 6009b - Patching 6000 - Electricity
6015 - Repairs Materials Supplies 6009c - Speed Humps 6009 - Repairs
& Maintenance
6062 - General Security & Monitor Sys 6009d - Tanglewood Sign
6009a - Seal Coating
6080 - Lake Treatment 6010 - Ground Maintenance 6009b - Patching
7000 - Printing & Postage 6014 - Gate repairs 6009c - Speed Humps
7020 - Office Supplies & Expenses 6015 - Repairs Materials Supplies
6009d - Tanglewood Sign
9400 - Drainage clean 6062 - General Security and Monitor Sys 6010 -
Ground Maintenance
11000 - Contingency 6062a - Camera System 6014 - Gate Repairs
12000 - Bank Charges 6071 - Towing 6015 - Repairs Materials Supplies
Condominium Filing Fee 6080 - Lake Treatment 6062 - General Security
and Monitor Sys
Bad Debts 7000 - Printing and Postage 6062a - Camera System
Bank Charges 7020 - Office supplies and Expenses 6071 - Towing
Drain Certification (2011 Budget Placeholder) 9400 - Drainage clean
6080 - Lake Treatment
11000 - Contingency 7000 - Printing and Postage
12000 - Bank Charges 7020 - Office Supplies & Expenses
9400 - Drainage Clean
11000 - Contingency
12000 - Bank Charges
Thanks for the previous information that I have asked for and for that
I have gained from reading the post here. I did not know you could do
so much with Excel.
I have recently taken on the duties as Treasurer for our Homeowners
Association and I am trying to set up a workbook that I can properly use
to show the homeowners and the board of director where the money is
coming from and going to and do some comparisons between years. I
currently have 8 worksheets and I am trying not to have to enter
duplicate information on each sheet so I have used my "Income Stmt"
worksheet as kind of a master. And so far that has work out pretty
well, but each Budget year have some similar expenses and some different.
Here is how my workbook is set up, each worksheet is label and it's
purpose as follows:
Itemized - Checkbook - Records all expenses/deposits - This is a data
validation from a List
Bank Acct - Contains ending balances of our accounts
Income Stmt - This is what I consider my master sheet and pulls data
from the corresponding sheet base on Month and Year entered
Monthly Exp - Takes the inputs from Itemized and broke out by month
using formulas like this -
=SUMIF(Itemized!$D:$D,"="&($A4&TEXT(B$2,"mmm-yy")),Itemized!$C:$C)
Monthly Inc - Takes the inputs from Itemized and broke out by month
using formulas like this -
=SUMIF(Itemized!$D:$D,"="&($A4&TEXT(B$2,"mmm-yy")),Itemized!$C:$C)
Budget06 - These are my budget worksheets that I use to tally the
required maintenance and projects to set the current years association
assessment fee and will continue to add as the years go by.
Budget07
Budget08
My problem is that every year the number of line items in the budget
are different and what I would like to do is depending on the date
entered on the Income Stmt that the corresponding budget replace the
line items on my Income Stmt worksheet. I have named two ranges on each
budget worksheet BudgetYR_Inc and BudgetYR_Exp. I have pasted below two
different budget years and my income stmt. I do not have any like named
ranges on the Income Stmt, but think I could do the same as my Budget
worksheet and have the ranges replaced depending date entered. I do
this for the dollar amounts but have not figured out how to do that for
the line items.
=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A:A"),$B9,INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!D
"))As you can see spacing is different on each budget and Income Stmt.
Any help with explaining to me or showing me how to accomplish the
sway would be greatly appreciated.
Thanks in Advance
Harry
Budget06 Budget07 Income Stmt
Income Income
100 - Late fees 100 - Late Fees
200 - Interest Income 200 - Interest Income 100 - Late Fees
300 - Misc Income 300 - Misc Income 200 - Interest Income
400 - Violation Income 400 - Violation Income 300 - Misc Income
500 - Gate Key Income 500 - Gate Key Income 400 - Violation Income
600 - Assessments 600 - Assessments 500 - Gate Key Income
610 - Special Asses 610 - Special Asses 600 - Assessments
1180 - Reserve Asses 1180 - Reserve Asses 610 - Special Asses
Total Income Total Income 1180 - Reserve Asses
Expenses Expenses
1140 - Insurance 1140 - Insurance
1180 - Reserve Txfr 1180 - Reserve txfr
5001 - Management Fees 5001 - Management Fees Expenses
5010 - Legal Fees 5010 - Legal Fees 1140 - Insurance
5020 - Accounting Fees & Tax Return 5020 - Accounting Fees & Tax
Return 1180 - Reserve Txfr
5040 - Corporate Annual Report 5040 - Corporate Annual report 5001 -
Management Fees
5060 - Telephone 6000 - Electricity 5010 - Legal Fees
6000 - Electricity 5060 - Telephone 5020 - Accounting Fees & Tax Return
6009 - Repairs and Maintenance 6009 - Repairs & Maintenance 5040 -
Corporate Annual Report
6010 - Ground Maintenance 6009a - Seal Coating 5060 - Telephone
6014 - Gate Repairs 6009b - Patching 6000 - Electricity
6015 - Repairs Materials Supplies 6009c - Speed Humps 6009 - Repairs
& Maintenance
6062 - General Security & Monitor Sys 6009d - Tanglewood Sign
6009a - Seal Coating
6080 - Lake Treatment 6010 - Ground Maintenance 6009b - Patching
7000 - Printing & Postage 6014 - Gate repairs 6009c - Speed Humps
7020 - Office Supplies & Expenses 6015 - Repairs Materials Supplies
6009d - Tanglewood Sign
9400 - Drainage clean 6062 - General Security and Monitor Sys 6010 -
Ground Maintenance
11000 - Contingency 6062a - Camera System 6014 - Gate Repairs
12000 - Bank Charges 6071 - Towing 6015 - Repairs Materials Supplies
Condominium Filing Fee 6080 - Lake Treatment 6062 - General Security
and Monitor Sys
Bad Debts 7000 - Printing and Postage 6062a - Camera System
Bank Charges 7020 - Office supplies and Expenses 6071 - Towing
Drain Certification (2011 Budget Placeholder) 9400 - Drainage clean
6080 - Lake Treatment
11000 - Contingency 7000 - Printing and Postage
12000 - Bank Charges 7020 - Office Supplies & Expenses
9400 - Drainage Clean
11000 - Contingency
12000 - Bank Charges