M
Monomeeth
I am using MS Excel 2003 SP2 and have designed a spreadsheet to use as a
budget tracking tool.
The spreadsheet has a worksheet (Invoice Tracker) where all invoices and
expenses are entered, and another worksheet (Budget Tracker) for users to
track how our expenses are going in relation to our budget. I want this
worksheet to measure how we're tracking on a yearly basis (using SUMIF
formulas referring to cells in other worksheets and workbooks), and on a
monthly basis (using SUMPRODUCT formulas, also referring to cells in other
worksheets and workbooks).
Everything works well with the YEAR TO DATE figures, but with the CURRENT
MONTH figures I'm not sure where to start with one of the columns
("budgeted"). Here is a snapshot:
C U R R E N T M O N T H F I GU R E S
BUDGETED ACTUAL VARIANCE
COACHING $1340.87
MENTORING $1206.23
CATERING $ 728.00
I have two fields for users to use to enter the start date (F1) and the end
date (F2). What I want is a formula which has the following logic to it:
-- IF F1 = "1/4/2008" AND F2 = "30/4/2008" THEN get the monthly budgeted
figure for "COACHING" from cell E11 in Sheet 1 of the Budget Workbook
-- IF F1 = "1/5/2008" AND F2 = "31/5/2008" THEN get the monthly budgeted
figure for "COACHING" from cell F11 in Sheet 1 of the Budget Workbook.
-- IF F1 = "1/6/2008" AND F2 = "30/6/2008" THEN get the monthly budgeted
figure for "COACHING" from cell G11 in Sheet 1 of the Budget Workbook.
And so on, until all 12 months are catered for. Obviously, if F1 and/or F2
do not fulfil any of the criteria, the cells will display an error.
I am using both the start and end dates because I need them for the
SUMPRODUCT formulas I am using to calculate the ACTUAL figures. An example
formula I'm using is below:
=SUMPRODUCT(--('Invoice Tracker'!A2:A2000>=F1),--('Invoice
Tracker'!A2:A2000<=F2),--('Invoice Tracker'!F2:F2000="COACHING"),'Invoice
Tracker'!E2:E2000)
However, whilst I need to have the start and end dates for the above
formulas, I guess I don't need both of them for the formulas I'm stuck on.
For instance, I could just use the start date to determine the fields I want?
Or, if it was easier, I could use another field (with activation) for users
to pick the month from a drop-down list and have this determine the field
reference?
Any help would be greatly appreciated.
Many thanks,
Joe.
budget tracking tool.
The spreadsheet has a worksheet (Invoice Tracker) where all invoices and
expenses are entered, and another worksheet (Budget Tracker) for users to
track how our expenses are going in relation to our budget. I want this
worksheet to measure how we're tracking on a yearly basis (using SUMIF
formulas referring to cells in other worksheets and workbooks), and on a
monthly basis (using SUMPRODUCT formulas, also referring to cells in other
worksheets and workbooks).
Everything works well with the YEAR TO DATE figures, but with the CURRENT
MONTH figures I'm not sure where to start with one of the columns
("budgeted"). Here is a snapshot:
C U R R E N T M O N T H F I GU R E S
BUDGETED ACTUAL VARIANCE
COACHING $1340.87
MENTORING $1206.23
CATERING $ 728.00
I have two fields for users to use to enter the start date (F1) and the end
date (F2). What I want is a formula which has the following logic to it:
-- IF F1 = "1/4/2008" AND F2 = "30/4/2008" THEN get the monthly budgeted
figure for "COACHING" from cell E11 in Sheet 1 of the Budget Workbook
-- IF F1 = "1/5/2008" AND F2 = "31/5/2008" THEN get the monthly budgeted
figure for "COACHING" from cell F11 in Sheet 1 of the Budget Workbook.
-- IF F1 = "1/6/2008" AND F2 = "30/6/2008" THEN get the monthly budgeted
figure for "COACHING" from cell G11 in Sheet 1 of the Budget Workbook.
And so on, until all 12 months are catered for. Obviously, if F1 and/or F2
do not fulfil any of the criteria, the cells will display an error.
I am using both the start and end dates because I need them for the
SUMPRODUCT formulas I am using to calculate the ACTUAL figures. An example
formula I'm using is below:
=SUMPRODUCT(--('Invoice Tracker'!A2:A2000>=F1),--('Invoice
Tracker'!A2:A2000<=F2),--('Invoice Tracker'!F2:F2000="COACHING"),'Invoice
Tracker'!E2:E2000)
However, whilst I need to have the start and end dates for the above
formulas, I guess I don't need both of them for the formulas I'm stuck on.
For instance, I could just use the start date to determine the fields I want?
Or, if it was easier, I could use another field (with activation) for users
to pick the month from a drop-down list and have this determine the field
reference?
Any help would be greatly appreciated.
Many thanks,
Joe.