YTD Budget Totals

[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.

 

[Frank Kabel]

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

 

[JMay]

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

 

[JMay]

Sorry, but all $B$8's should read $B$2;

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