Excel YTD and Monthly Report 1

[willir]

I have a couple of instances where the end user want to track summations for the current month and year to date in a tabular format. Instead of manually adjusting the formulas each month, they want to use "smart" formulas.

Example:
Time sheet for operations

Cell
01   Name YTD CurMonth 01/05/04 01/12/04 ... 05/03/04 05/10/04 
02   John 688   80       40        40           40       40
03   Mary 585   65       40        40           40       25

The YTD formula is fairly straight forward.

It is the current month formula is what I have not been able to fogure out. The SUM cell range is currently adjusted for the next month. What I want to do is find the sum for hours where the start-of-week date matches (MONTH(NOW()))

A solution for this sumation problem could also help us track raw materials, etc.

Thanks in advance.
Richard

 

[SkipVought]

Richard,

I am sorry to inform you that you have made the TYPICAL user mistake of defining your data structure in a NON-NORMAILZED manner which makes this kind of reporting VERY VERY DIFFICULT.

The best advise I could give you for this problem AND for all the problems to come, is to reorganize your data in a NORMALIZED table from which reporting with such tools as Subtotal, pivotTable, Sort, Filter as well as lookup functions, will be ten thousand times simpler.

Here's a sample of a reformatted table...
[tt]
Name Date Hours
John 01/05/04 40
John 01/12/04 40
John 05/03/04 40
John 05/10/04 40
Mary 01/05/04 40
Mary 01/12/04 40
Mary 05/03/04 40
Mary 05/10/04 25
[/tt]
With a table like this, you can easily get YTD, MTD using the PivotTable Wizard in SECONDS.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PeterMoran]

Hi willir,

While I have much empathy for Skip's point and agree with him in principle, unfortunately life is not always as easy as just redesigning a spreadsheet which may have been in use in an organisation for some time and may have other implications.

With this in mind here is how your request can be resolved.

First, insert a row under the headings (Row 2) and in each cell under every date (assuming they are actual dates and not text - they should be!) enter the formula =MONTH(cell above) where "cell above" is the cell address of the cell with the date. Now select all the month numbers we have created in the inserted row and use Insert, Name, Define to create a range called Months. This row can now be hidden if required.

Then in the "CurMonth" cell for the first person enter the following formula =SUMIF(Months,MONTH(NOW()),D3:Z3) where D3:Z3 are the columns that will at some time during the year have hours in them. For month 5 this formula should just total the columns which have 5 in the Months range. Once you are happy with this working in the first row then copy this formula to all the CurMonth cells below.

Good Luck!
Peter Moran

 

[SkipVought]

Peter,

I am willing to play the "good cop; bad cop" bad cop role. I know that often yer stuck with what you have. But someone has to "sound the warning", if only to give pause to someone who has the opportunity at some future moment to make a choice regarding a workbook design.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[willir]

Thank you gentlemen

Yes, I am familiar with normalization - I do a fair amount of database work, and I certainly don't mind it referenced as an example of a good design.

Peter is correct, in that this beast is not mine. In fact it is from a senior manager 2000 miles away from me. (So I am not going to tell him how to...) But I will share with him the work around solution. And a modified solution will also prove useful for some of my end users who do a lot of grunt work when they don't have to.

 

[dcompto]

There are no bad cops here. We all benefit greatly from the multiple views and solutions offered. Thanks, guys and gals!