Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    YTD Totals (9.0.6926 (SP-3)

    It's tax time!

    I keep an Excel workbook which I use for invoicing. Each sheet represents one invoice. Each invoice has several days with entries for Flat Rate, Travel (#Km traveled x $/Km), and Out of Pocket. At the end of each day I have sub-totals and at the end of each sheet I have grand totals for the invoice period. So far, so good.

    Now I've added another sheet for YTD totals. I've attached a small sample where I manually added the grand totals from each previous sheet. I had to paste them in using special then values so that I didn't get reference errors.

    This is really clunky and time-consuming when you consider that my real invoice workbook could have dozens of sheets for the year.

    There must be some way I can automate this YTD process. In fact I'm sure the whole workbook could be streamlined. I just don't know how to do it! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Attached Files Attached Files

  2. #2
    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: YTD Totals (9.0.6926 (SP-3)

    This doesn't answer your actual question, BUT
    I found the best way to work with something like this, would be to put the data in a "table" type of format.
    Columns for date, customer, comments, location, etc and flat-rate, travel, out-of-pocket, total, etc

    Having in 1 table allows the use of filtering, (auto and advanced) d-functions, array functions, pivot tables, etc, sub totals: all sorts of things. You could even add columns for other items to help filter (month, year, task)

    If you need a "printout" in a certain form, I would just create with formulas (or a macro) to extract the data for that form. You could select by month or something else

    Advantages, less worksheets, form can be updated easier (only 1 form) and calculations are much easier to work with.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: YTD Totals (9.0.6926 (SP-3)

    Thanks Steve but my real problem is I don't know Jack about "d-functions, array functions, pivot tables, formulas, macros, etc.". Conceptually I understand but practically I only have the skills to create the workbook in a printable format.

    Do you know of any site I can go to where I can learn how? (We're talking a free tutorial here). Advanced Excel for Dummies - Online Version!

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: YTD Totals (9.0.6926 (SP-3)

    From <!post=A,14581>A<!/post> to <!post=Z,229297>Z<!/post> you're in the right place to learn a lot.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: YTD Totals (9.0.6926 (SP-3)

    Hi Colin,

    I agree with Steve about the need for more thought to be given to the layout - you might put each item in it's own column, rather than repeating the items every n rows.

    Disregarding that, and working with your basic layout, here's a re-worked version of your spreadsheet. Main changes are:
    1. Summary rows for each subsidiary sheet and YTD sheet moved to the top.
    2. Aggregations on each subsidiary worksheet and the top of the YTD sheet constructed using SUMIF formulae
    3. Consolidations from subsidiary worksheets on YTD sheet constructed via convoluted formulae that work out where to get the results from with smoke & mirrors (see E11 for an example - but have a stiff drink first).

    Cheers

    PS: Excel's own help file is a useful reference, but it's by no means a tutorial on how to use the various functions creatively.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    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: YTD Totals (9.0.6926 (SP-3)

    With a good layout, you don't need to use ALL the possibilities. One or 2 techniques can get you what you need. Different people use different techniques to accomplish identical goals.

    If you become reasonably proficient in a few you can accomplish a lot in excel. You don't need to know ALL the different techniques for accomplishing a goal.

    A good layout also aids in NOT having to do more "strange methods" to accomplish the goals, but can use the more "basic" features.

    Steve

Posting Permissions

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