Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need 2 compare month 2 month multiple years (2000/XPHome)

    Suggestions most welcome.
    Need to set up a spreadsheet for a P&L to compare month this year to month last year and month the year before etc
    I have all the relevant data but not sure how to set it up.
    I would like the end result to show sales jan/04 : jan/03; jan/02 and the same with expenses on the same sheet/graph. Should I create a sheet for each year? Sales Expenses etc by row months by colum. If so can someone help with the comparison setup. I'm beginner at this stuff...and if the wheel is already invented....!!
    Thanx, Joan
    Hope I explained it ok.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Joan

    OK I can see the picture, but its fuzzy.

    I would suggest, and these suggestions are based on what I understood you want to do, is to have a sheet for each Month. These sheets will be identical in structure, that means the same entry is on the same row, in the same column on all sheets.

    Cash on Hand is in cell J19 on <font color=blue> ALL</font color=blue> sheets.

    Now on your comparison sheet you can have two columns one for this year and one for the past year and the formulas will be in the form:

    =SheetName!J19

    Where SheetName is the worksheet that you have the data to be compared on, say Jan 03 and J19 is the cell to compare.

    So to compare Cash on Hand for Jan 2003 and Cash on Hand for Jan 2002 the formulas will be:

    =Jan 02!J19 and =Jan 03!J19

    I hope you understand what I have done here, if not, please ask questions.

    Then you can chart the values in the comparison sheet.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2

    It sounds like your data would lend itself readily to the use of Pivot Tables. That way, you could restructure your data each month without having extenhsive formula overhead. There is some fairly good explanations of Pivot Tables in Online Help. In the newer versions of Excel, there are also Pivot Charts. HTH
    Gre

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    I would keep all months, all years in the same sheet. I am not a big advocate of having identically structured workbooks and worksheets if the data is the same type and you will ever want to summarize it.

    Having all the data together allows the use of excels auto-filtering/subtotaling features as well as adv filtering, pivot tables, database functions, etc.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    Thanx Wassim: I was thinking along those lines, but also thought that each year would incrementally increase the number of worksheets by 12. What about 12 sheets with the colums labelled by the year. ?
    I am interested too in what Steve & Unkamunka say about pivot tables.. which is an unknown zone for me.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    Thanx Steve: Could you expand a little on keeping all the data on one sheet? In my example I only used 03 & 02. My end result will be a 5 year comparison, or steer me to a tutorial. A single sheet for 12 months X 5 years would, in my mind be very difficult to deal with.
    I have never used pivot tables, but from what you say it could be what I I have a lot of data to enter. If I do just 12 sheets as I say in my reply to Wassim, would they lend themsleves to pivot tables when I learn more about them and how to?
    Joan

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    Look at this example. The data uses filters on year an month to "hide" the unneeded data at the time.
    The Pivot Table has a wizard (right click) on Table and chose wizard to look at it.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2

    Steve has much deeper knowledge than I on this. I would simply confirm that having multiple Worksheets can sound tidy up front but fast turns into a nightmare. Senior personnel can come up with the most bizarre requests for reports - with little understanding for how the underlying data is structured. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I sense that you are thinking that navigating through one Worksheet is daunting. This can be overcome by using a combination of FreezePanes and Named Ranges. Named Ranges would work in that you could easily navigate to whichever year (or period) you choose - through the top-left navigation box (to the left of the formula bar). Please psot back if you are not familiar with FreezePanes (under Window on the Main Menu Bar).

    HTH
    Gre

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    Steve: Thank you so much. Your example is just great ! I will refer to it constantly, while I learn more about filters and pivot tables to get to the comfort zone. A picture tells a thousand words.... Just what I needed to get me going on this project that was 'dumped' on me.
    Thank you again (and everyone else)
    Joan

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    You are welcome. If you need more help fell free to post again. The more specific the question the better we can answer.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    Steve: I have spent a few quality hours with pivot tables but somehow think that it may not be the route to go.
    I have attached (I hope correctly) my 'play' worksheet.
    Below the data I show two reports in the format I am aiming for. One for January, one for February.
    Is this format doable? If so then it will be worth the struggle to continue learning this pivot table stuff.
    Thanx, Joan

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    To use a pivot the data must be in a different form see the attached sheet. I set up the page field for the month so you can choose the month to view
    I did not include "grs profits" since this is "Grand total" I don't think you can edit this name.

    If you want the data in the format you have listed and 1 table with a "month selection" or even 12 separate tables in that form,or you want specialized listings and formatting you will probably have to use a formulas or a macro.

    Formulas would involve INDEX assuming they are in the setup you desire:
    See the "formula" sheet attached. Note the "yellow" info could be hidden.

    A macro is also an option.

    Part of the choice for pivot table vs "yours" might be how custom the pivot table can be and how you really want the source data to be

    Steve

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Br. Columbia, Canada
    Posts
    109
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need 2 compare month 2 month multiple years (2000/XPHome)

    Thank you Steve: You have really helped me get started on this. Sorry took so long to get back, I sort of lost my way back here, kept getting that message.
    Joan

Posting Permissions

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