Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing sub-totals automatically (Excel 2K/Windows 2K)

    First thing
    Silverback

  2. #2
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing sub-totals automatically (Excel 2K/Windows 2K)

    Why not invest in Quicken or Microsoft Money as a budget control tool?They offer many additional features you might otherwise have to invent yourself, such as reports pre-formatted with lots of period options-month/week/quarter ,etc. They also can track credit cards and combine charged and cash expenses into one report. If you wish to continue using Excel to do this, check around Google for excel checkbooks or something....there might be a template out there that does what you want. Good luck and have a nice retirement.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing sub-totals automatically (Excel 2K/Windows 2K)

    Silverback,

    Here's a little something to play with. Not much automation to worry about, I'm afraid. The subtotals are pulled in using sumifs, and the categories on each sheet are restricted using data validation linked to the named range on the totals sheet - to add a new category, you can simply insert a column anywhere between the current columns C to I. I kept the monthly sheets to a minimum for size reasons - all you'll need to do after inserting a new sheet and labelling it, eg "mar", is to copy the february formulae down and replace the "feb" in the formulae with "mar".

    This approach does mean you don't have your subtotals on the monthly sheets, but hopefully it will get you started.

  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: Accessing sub-totals automatically (Excel 2K/Windows 2K)

    Have you tried a pivot table? Pivot table is meant for summarizing data in master table into subcategories and will grow and expand as needed.
    Select the data table and data-pivot table report for the wizard.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing sub-totals automatically (Excel 2K/Windows 2K)

    Hi everyone
    Thanks for your helpful replies. Sorry it's taken me so long to get back.
    Torquemada: Pay money - aaaaaaaaaargh! <img src=/S/scream.gif border=0 alt=scream width=15 height=15> Seriously, it's not worth me purchasing one of these as we are only playing with this - it's not a legal requirement for us! Actually, I'm only being lazy - the system we have works well enough and you might think I had enough time once a month to sort the things out manually anyway, so I'm going to stick with Excel for the moment, thanks - and thanks for your good wishes about retirement.
    Brooke: Thanks for taking the time to produce your example. (I note from your personal details that you are an accountant; hence the mastery of Excel.) It's interesting and shows up the fact that I don't understand the built in Excel commands like SUMIF. I need to get to grips with these so that's a little project I have in hand.
    Steve: A PivotTable! Of course! Since you replied, I have been trying PTs and I have to say that the Excel help on these is not as helpful as I think it could be. I have progressed to the point where I realised that it was worth taking note of options presented by the wizard instead of just clicking NEXT, so once I found the 'multiple consolidation ranges' option, I got a lot further with summing more than one month's worth of data. It's still not quite right, so I'm still experimenting, but I think I am going to go with PTs as the solution. I tried producing a PT of PTs, but that got a little hairy , but I'll continue to practice.
    As usual, loungers come up trumps.
    Thanks all for your help.
    Silverback

Posting Permissions

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