# Thread: YTD Totals (9.0.6926 (SP-3)

1. ## 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>

2. ## Re: YTD Totals (9.0.6926 (SP-3)

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. ## 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. ## 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. ## 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.

6. ## 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
•