I use an Excel 2007 table to keep a log of daily activities. (See the
simplified example below).

I want to construct for my own use a simple reporting system that will
quickly display the answers to such queries as this:

Show me the total hours I spent on each activity on 2009-11-13.
Show me how many hours I spent on running last week (or last month).
Show me a week-by-week breakdown since the beginning of the year of my
totals for writing and music.

How can I do this with minimum fuss? I don't expect anyone to walk me
through it step by step, but it would be helpful to know what tools will be
useful: PivotTables? Macros? I don't want to have to enter formulas
repeatedly. Ideally I'd like to be able to make choices from drop-down lists
or menus.

I'd welcome any guidance on the basic approach, as well as web addresses for
any useful demos or existing samples I can use as models.

Thanks.

David

THE DATA SOURCE:

DATE TASK HOURS

2009-11-13 music 1.5
2009-11-13 running 1.0
2009-11-13 music 0.7
2009-11-13 work 5.2
2009-11-13 work 2.0
2009-11-13 writing 2.5
2009-11-14 work 3.2
2009-11-14 writing 2.3
2009-11-14 music 1.6
2009-11-15 running 1.2
2009-11-15 work 4.5
2009-11-15 music 2.0
2009-11-15 music 0.5
2009-11-15 writing 2.2

i would use autofilters.

--


Gary Keramidas
Excel 2003


"Axel07" wrote in message
...
I use an Excel 2007 table to keep a log of daily activities. (See the
simplified example below).

I want to construct for my own use a simple reporting system that will
quickly display the answers to such queries as this:

Show me the total hours I spent on each activity on 2009-11-13.
Show me how many hours I spent on running last week (or last month).
Show me a week-by-week breakdown since the beginning of the year of my
totals for writing and music.

How can I do this with minimum fuss? I don't expect anyone to walk me
through it step by step, but it would be helpful to know what tools will be
useful: PivotTables? Macros? I don't want to have to enter formulas
repeatedly. Ideally I'd like to be able to make choices from drop-down lists
or menus.

I'd welcome any guidance on the basic approach, as well as web addresses for
any useful demos or existing samples I can use as models.

Thanks.

David

THE DATA SOURCE:

DATE TASK HOURS

2009-11-13 music 1.5
2009-11-13 running 1.0
2009-11-13 music 0.7
2009-11-13 work 5.2
2009-11-13 work 2.0
2009-11-13 writing 2.5
2009-11-14 work 3.2
2009-11-14 writing 2.3
2009-11-14 music 1.6
2009-11-15 running 1.2
2009-11-15 work 4.5
2009-11-15 music 2.0
2009-11-15 music 0.5
2009-11-15 writing 2.2

I would use Pivot Tables. They will do everything you want, and more. The
drop down menus you want are Page Fields.

Regards,
Fred

"Axel07" wrote in message
...
I use an Excel 2007 table to keep a log of daily activities. (See the
simplified example below).

I want to construct for my own use a simple reporting system that will
quickly display the answers to such queries as this:

Show me the total hours I spent on each activity on 2009-11-13.
Show me how many hours I spent on running last week (or last month).
Show me a week-by-week breakdown since the beginning of the year of my
totals for writing and music.

How can I do this with minimum fuss? I don't expect anyone to walk me
through it step by step, but it would be helpful to know what tools will
be
useful: PivotTables? Macros? I don't want to have to enter formulas
repeatedly. Ideally I'd like to be able to make choices from drop-down
lists
or menus.

I'd welcome any guidance on the basic approach, as well as web addresses
for
any useful demos or existing samples I can use as models.

Thanks.

David

THE DATA SOURCE:

DATE TASK HOURS

2009-11-13 music 1.5
2009-11-13 running 1.0
2009-11-13 music 0.7
2009-11-13 work 5.2
2009-11-13 work 2.0
2009-11-13 writing 2.5
2009-11-14 work 3.2
2009-11-14 writing 2.3
2009-11-14 music 1.6
2009-11-15 running 1.2
2009-11-15 work 4.5
2009-11-15 music 2.0
2009-11-15 music 0.5
2009-11-15 writing 2.2

Excel 2007 PivotTable
An example:
http://www.mediafire.com/file/wnwcznwfnjk/11_14_09.xlsx

Many thanks, Herbert! This is just what I need.

David

"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
An example:
http://www.mediafire.com/file/wnwcznwfnjk/11_14_09.xlsx
.

Hi, Herbert. Thanks again for your very helpful example. I've been trying to
duplicate your PivotTable designs. Your PivotTables come out with headings
"Past", "Sloth" and "Sum of Waste" (I admire your candor in choosing these
particular names!). "Past" and "Sloth" each has a drop-down button. When I
create the PivotTables, however, only one of these gets a drop-down button;
when I click on it it takes me to a dialog in which I can select either
"Past" or "Sloth" to display the appropriate filtering options. The
functionality seems to be the same, but I'd rather have the separate
drop-down buttons. What accounts for the difference?

David

"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
An example:
http://www.mediafire.com/file/wnwcznwfnjk/11_14_09.xlsx
.

David,
In my design, enable Show/Hide Field List.
For each PT, study the Row Labels list:
You can drag one or more Fields into it, in the desired order.
Open your design and compare.
Also study the multiple PT source structure.
Herb

Thanks, Herb. I was able to force a dropdown on each of the field labels by
first going to PivotTable Options and checking Classic PivotTable style; then
laying out my table; then unchecking Classic style. Awkward, but it works.

I'd be interested in studying the "multiple PT source structure", but I
don't know what you mean by that. Thanks again, by the way, for creating that
model for me that answered all my requirements -- it saved me hours!

David

"Herbert Seidenberg" wrote:

David,
In my design, enable Show/Hide Field List.
For each PT, study the Row Labels list:
You can drag one or more Fields into it, in the desired order.
Open your design and compare.
Also study the multiple PT source structure.
Herb
.