#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default Year to date sum

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Year to date sum

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default Year to date sum

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Year to date sum

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default Year to date sum

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Year to date sum

I'm glad you figured it out.

Thanks for the feedback....much appreciated.

***********
Regards,
Ron

XL2002, WinXP


"Esrei" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
holiday dates bucci Excel Worksheet Functions 4 June 15th 06 09:35 AM
How to return a value between date ranges Mary-Lou Excel Worksheet Functions 7 May 26th 06 10:00 PM
Cell won't accept date past year 2009 lbrown New Users to Excel 2 February 15th 06 02:51 PM
How do I put in the date without the year? Barb Byrd Excel Discussion (Misc queries) 2 March 13th 05 05:46 AM
dHow do I put in the date without the year? Barb L Excel Discussion (Misc queries) 2 March 5th 05 11:45 PM


All times are GMT +1. The time now is 09:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"