Thank you, thank you

Now I completly understand offset, and my problem was that my financial year
starts in Sep-06

Enjoy the day, you made somebodies life easier

"Ron Coderre" wrote:

OK....let's analyze your formula:
=SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1)))

This part:
OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))

Starts with the 12-column range $D$18:$O$18,
offsets it by 0 rows,
offsetss it by 0 columns,
ensures that it is 1 row in height,
and resizes the width to the number of columns related to the month number
in D1.

So...
if $D$18:$O$18 contains the amounts for Jan_07 thru Dec_07
and the D1 date is 9/1/2006
(note: the year is irrelevent..only the month num is used)

then the formula should sum from Jan_07 thru Sep_07 by
effectively changing the range to $D$18:$L$18 (which is 9 cols wide).

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Esrei" wrote:

Dear Ron

Thank you for the reply. I am doing someting wrong because the formula sums
up to May-07 but my date in D1 is 09/01/2006

My dates in IS Bud 2007! is in row2
I did check all my dates are formatted correctly.

+SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1)))

Thank you

"Ron Coderre" wrote:

Try something like this:

With
Budget data on Sheet2
Row_1 contains Category, Jan, Feb, etc...Dec
Row_2 contains values: (AcctNum), (amt), (amt)....etc


On Sheet1
D1: (a date....example: 6/1/2006)
This formula sums the amounts from Row_2 on Sheet2 for Jan through the month
referenced in D1
D2: =SUM(OFFSET(Sheet2!B2:M2,0,0,1,MONTH(Sheet1!$D$1)) )

If D1 contains a date in June, the formula sums from Jan through Jun
Change D1 to a date in September and the formula sums from Jan through Sep

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Esrei" wrote:

I am bussy with a income statement with a date end of month in D1. My budget
is another worksheet with one column for each month. I am struggeling to
figure out a sum formula combind with something like offset, so that excel
automatily calculates the year to date budget total if I change the date on
the incomestatment.
Best regards