YTD Budget Totals

  • Thread starter Thread starter Dewayne Bien
  • Start date Start date
D

Dewayne Bien

Hi!
My budget form has a column for YTD (year-to-date)
Budgeted amounts which increases by 1 month each month as
I go through the year. Every month I need to add another
column to the formula to include the YTD total for the
current month. Does anyone have way for Excel to expand
the YTD range monthly without having to adjust my formula
manually. FYI, the columns are not contiguous so the
formula must account for that.
Thank you in advance for any help you can give me.
 
Hi
can you give some more details. That is: which columns store your
monthly data / state the exact cells for at least some months so we get
to know the logic for your non contiguous columns :-)

Note: Non contiguous ranges are quite difficult to handel in worksheet
formulas. In most cases it's easier to adapt the spreadsheet layout to
a more 'formula-friendly' layout
 
Restore Full Page to View:::
Sample::: Works for me,,,
In Sheet1:
A B C D
1 March 2004 YTD 3<< This number entered brings
over appropriate Col from Budget Sheet
2 Actual Budget
3 Sales 10,000 =HLOOKUP(D$1,Budget!$B$2:$M$13,2,FALSE)
4 COS 7,000 =HLOOKUP(D$1,Budget!$B$8:$M$13,3,FALSE)
5 Gross Profit 3,000 =HLOOKUP(D$1,Budget!$B$8:$M$13,4,FALSE)
6 Oper Exp 1,800 =HLOOKUP(D$1,Budget!$B$8:$M$13,5,FALSE)
7 Net Inc (Loss) 1,200 =HLOOKUP(D$1,Budget!$B$8:$M$13,6,FALSE)

In Sheet Named "Budget":
A B C D E
1 Budget Y-T-D
2 1 2 3
4
3 Sales 10,000 20,000 30,000 40,000
4 COS 7,000
5 Gross Profit 3,000
6 Oper Exp 1,800
7 Net Inc (Loss) 1,200
 
Sorry, but all $B$8's should read $B$2;

JMay said:
Restore Full Page to View:::
Sample::: Works for me,,,
In Sheet1:
A B C D
1 March 2004 YTD 3<< This number entered brings
over appropriate Col from Budget Sheet
2 Actual Budget
3 Sales 10,000 =HLOOKUP(D$1,Budget!$B$2:$M$13,2,FALSE)
4 COS 7,000 =HLOOKUP(D$1,Budget!$B$8:$M$13,3,FALSE)
5 Gross Profit 3,000 =HLOOKUP(D$1,Budget!$B$8:$M$13,4,FALSE)
6 Oper Exp 1,800 =HLOOKUP(D$1,Budget!$B$8:$M$13,5,FALSE)
7 Net Inc (Loss) 1,200 =HLOOKUP(D$1,Budget!$B$8:$M$13,6,FALSE)

In Sheet Named "Budget":
A B C D E
1 Budget Y-T-D
2 1 2 3
4
3 Sales 10,000 20,000 30,000 40,000
4 COS 7,000
5 Gross Profit 3,000
6 Oper Exp 1,800
7 Net Inc (Loss) 1,200
Click to expand...
 
Back
Top