+ Reply to Thread
Results 1 to 12 of 12

quarterly reports

  1. #1
    Al Vanderhoof
    Guest

    quarterly reports

    I have a workbook that has 52 weeks(rows) of income and expenses(columns).
    I'm trying to set up a quarterly reports sheet that updates from this info,
    but I cannot figure out how to set up the formulas that will find the dates
    that are in the range and save the subtotals and totals.
    Thanks ahead of time for any help



  2. #2
    David
    Guest

    RE: quarterly reports

    Hi Al,
    I wsa trying to set up a sheet that I htought might look like yours and came
    up with this, although I am sure it is much simpler than what you are really
    dealing with:

    Date "Income
    Weeks" Exp1 Exp2 Net
    1/7/2005 1 Exp1 Exp2 Net1
    1/14/2005 2 Exp1 Exp2 Net2
    1/21/2005 3 Exp1 Exp2 Net3
    1/28/2005 4 Exp1 Exp2 Net4
    2/4/2005 5 Exp1 Exp2 Net5
    2/11/2005 6 Exp1 Exp2 Net6
    2/18/2005 7 Exp1 Exp2 Net7
    2/25/2005 8 Exp1 Exp2 Net8
    3/4/2005 9 Exp1 Exp2 Net9
    3/11/2005 10 Exp1 Exp2 Net10
    3/18/2005 11 Exp1 Exp2 Net11
    3/25/2005 12 Exp1 Exp2 Net12
    4/1/2005 13 Exp1 Exp2 Net13

    This only shows the first quarter and the first week of the 2nd quarter. Is
    this similar to how you have your data set up? Then my second question would
    be, what is it you want to carry ove to your quarterly report? All of the
    detail of each quarter only or the subtotals of the quarter only?

    Thanks,
    "Al Vanderhoof" wrote:

    > I have a workbook that has 52 weeks(rows) of income and expenses(columns).
    > I'm trying to set up a quarterly reports sheet that updates from this info,
    > but I cannot figure out how to set up the formulas that will find the dates
    > that are in the range and save the subtotals and totals.
    > Thanks ahead of time for any help
    >
    >
    >


  3. #3
    Jim Thomlinson
    Guest

    RE: quarterly reports

    You are going to have some problems. The Quarters do not break evenly between
    weeks. Can you get a hold of the daily amounts. If so then we can set you up
    with at pivot table which will automatically subtotal based on weeks, months
    , quarters...What ever makes you heart go pitter patter... Otherwise you will
    have to break up 4 of your weeks that straddle the quarters.

    HTH

    "Al Vanderhoof" wrote:

    > I have a workbook that has 52 weeks(rows) of income and expenses(columns).
    > I'm trying to set up a quarterly reports sheet that updates from this info,
    > but I cannot figure out how to set up the formulas that will find the dates
    > that are in the range and save the subtotals and totals.
    > Thanks ahead of time for any help
    >
    >
    >


  4. #4
    Al vanderhoof
    Guest

    RE: quarterly reports


    If a week spans two quarters then it would be included in the first
    quarter.
    I basically want to carry everything over and then i would link it to a
    P&L sheet.
    Each quarter would have its own worksheet.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  5. #5
    Al vanderhoof
    Guest

    RE: quarterly reports



    If a week spans two quarters then it would be included in the first
    quarter.
    I basically want to carry everything over and then i would link it to a
    P&L sheet.
    Each quarter would have its own worksheet.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  6. #6
    Jim Thomlinson
    Guest

    RE: quarterly reports

    I think that we might still be bset off with a pivot table. Give this a try
    and let me know what you think.

    Select all of the data.
    Click on Data -> Pivot Table
    A wizard will open up. It is fairly self explanitory to go through the
    steps. (You cna probably just push finish and it will make all of the correct
    guesses. It will create a new sheet for you with a pivot table on it. There
    will be a new menu with dates and all of your accounts on it. Drag the date
    to the left column and the individual accounts to the middle. Select the
    Dates and right click. Select group on and you can group by quarter.
    Hopefully this is what you want. If this works for you in general and you wna
    to know more just ask...

    HTH
    "Al vanderhoof" wrote:

    >
    >
    > If a week spans two quarters then it would be included in the first
    > quarter.
    > I basically want to carry everything over and then i would link it to a
    > P&L sheet.
    > Each quarter would have its own worksheet.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    >


  7. #7
    Al vanderhoof
    Guest

    RE: quarterly reports


    It is a lot closer than anything I thought of so far.
    Will the pivot table update as new information is added?


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  8. #8
    Tom Ogilvy
    Guest

    Re: quarterly reports

    No. You have to refresh it and extend the source as well. You could do that
    with a dynamic range for the source.

    --
    Regards,
    Tom Ogilvy

    "Al vanderhoof" <calldpi@tds.net> wrote in message
    news:%23V%23n8u9DFHA.960@TK2MSFTNGP09.phx.gbl...
    >
    > It is a lot closer than anything I thought of so far.
    > Will the pivot table update as new information is added?
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!




  9. #9
    Al V
    Guest

    Re: quarterly reports

    I cannot get the pivot table to accept more than one data field(column) for
    the rest of the accounts.
    The rest of it works pretty well so far.
    I think I'm getting close.


    "Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in message
    news:EF1BD154-F2DA-4237-8BC4-5860701CF145@microsoft.com...
    >I think that we might still be bset off with a pivot table. Give this a try
    > and let me know what you think.
    >
    > Select all of the data.
    > Click on Data -> Pivot Table
    > A wizard will open up. It is fairly self explanitory to go through the
    > steps. (You cna probably just push finish and it will make all of the
    > correct
    > guesses. It will create a new sheet for you with a pivot table on it.
    > There
    > will be a new menu with dates and all of your accounts on it. Drag the
    > date
    > to the left column and the individual accounts to the middle. Select the
    > Dates and right click. Select group on and you can group by quarter.
    > Hopefully this is what you want. If this works for you in general and you
    > wna
    > to know more just ask...
    >
    > HTH
    > "Al vanderhoof" wrote:
    >
    >>
    >>
    >> If a week spans two quarters then it would be included in the first
    >> quarter.
    >> I basically want to carry everything over and then i would link it to a
    >> P&L sheet.
    >> Each quarter would have its own worksheet.
    >>
    >> *** Sent via Developersdex http://www.developersdex.com ***
    >> Don't just participate in USENET...get rewarded for it!
    >>




  10. #10
    JulieD
    Guest

    Re: quarterly reports

    Hi Al

    not sure exactly how you want the quarterly reports arranged but i'm
    assuming that you've got the same columns on this report as you have on the
    weekly report and what you want to see is:
    .............A.......................B.......................C................................D..........................
    1.........QTR...............Category1..............Category2..................Category3.........
    2.........1st..................=SUMPRODUCT(--(WeeklySheet!$A$2:$A$54>DATEVALUE("1/1/2005")),--(WeeklySheet!$A$2:$A$54<DATEVALUE("31/3/2005")),WeeklySheet!B$2:B$54)

    the formula in B2 can then be filled across to columns C, D, E etc
    and down for other quaters, just change the values in the DateValue part of
    the formula.

    BTW WeeklySheet needs to be replaced with the actual sheet name of your
    weekly numbers - if the sheet name contains a space you'll have to enclose
    it in ' '

    Cheers
    JulieD

    "Al Vanderhoof" <calldpi@tds.net> wrote in message
    news:420beba5$1_1@newspeer2.tds.net...
    >I have a workbook that has 52 weeks(rows) of income and expenses(columns).
    > I'm trying to set up a quarterly reports sheet that updates from this
    > info, but I cannot figure out how to set up the formulas that will find
    > the dates that are in the range and save the subtotals and totals.
    > Thanks ahead of time for any help
    >




  11. #11
    Chris Rogers
    Guest

    Re: quarterly reports

    It seems like you could easlily base a pivot table off of this.
    Assuming you use dates as the date field and not Week 1...Week 52, you
    could then use grouping within the pivot table to group your report by
    month, quarter or year.


  12. #12
    Ola
    Guest

    RE: quarterly reports

    Here are 2 pieces that might help:

    1. Insert a New Column with Quarter (Col B)
    Created from Week date: ="Q"&ROUNDUP(A2/13,0)
    Created from Calender date: ="Q"&ROUND(MONTH(A2)/3,0)

    2. Where col B is Q1, Sum every row in col C:
    =SUMIF(B1:B52,"=Q1",C1:C52)

    Ola Sandstrom


    Note:
    Quarter from weekdate will not entierly correct, as Jim points out.
    Quarter from calendar date will be correct.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1