Dynamic pivot table

Hi,
I really wounder how good Excel really is. I have for some years now worked
with a simple worksheet where I register my hours of work for all the
prosjects I participate in. In my workbook I have a sheet for all the months
in a year where I have a date, project, activty and description column. The
last sheet in workbook I have a summary where I calculate my salary based on
each month and a total salary. This works fine.

What I want is to expand the functionality by making a summary for each
month where I calculate all the hours for each project by month.
To do this manually I can us a pivot table to get what I want at this
current time. However I want to make a dynamic solution so I at any time can
go in and see the summary without making a new pivottable. The dynamic
pivottable shall show the summary for the current month.

This might seem a little complex functionality, but if this really works, I
will really bend myself in the dust for Excel and all the fantastic
functionalities which is avalable.
Anybody have some sort of tips, example or any guidens if this should be
possible???

Kind regards,

Jon Haakon

Hello Jon,

Take a look at Debra's brilliant work :
http://www.contextures.com/xlPivot01.html

HTH
Cheers
Carim

Hi Jon

Personally, I would keep all of the information on one sheet and use the
Pivot table to give me the data analysis for a given month by grouping
Dates monthly, and making the Date a Page field.

However, you can still achieve what you want with separate sheets.
First, create dynamic ranges for your data for each sheet, with 12
defined names, 1 for each month e.g.
Insert>Name>Define Jan >Refers to
=OFFSET(Sheet1!$A$1,0,0,count($A$A),4)
This assumes that Date is in column A, and you have 4 columns per sheet
as you describe.
Change Sheet1 to the name of the sheet that you use. Repeat the
procedure for Feb through December.

Insert another Summary sheet (Sheet13) or whatever title you want.
Create another defined name called Data and give it a value of
=INDIRECT(Sheet13!$A$1)
Enter Jan in cell A1 of Sheet 13
Create your Pivot table, giving the source range as =Data, and in the PT
wizard, say to create the table at cell A5 of Sheet13.
Format the PT the way you want.
To view any month, change the value in cell A1 to Feb, Mar etc. and
press the PT refresh button, and you will see your data for the relevant
month.

For detailed examples on how to set up dynamic ranges, and more help on
Pivot Tables, take a look at Debra Dalgleish's site
http://www.contextures.com/xlNames01.html#Dynamic

http://www.contextures.com/xlPivot01.html

--
Regards

Roger Govier


"Jon Haakon Ariansen" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I really wounder how good Excel really is. I have for some years now
> worked with a simple worksheet where I register my hours of work for
> all the prosjects I participate in. In my workbook I have a sheet for
> all the months in a year where I have a date, project, activty and
> description column. The last sheet in workbook I have a summary where
> I calculate my salary based on each month and a total salary. This
> works fine.
>
> What I want is to expand the functionality by making a summary for
> each month where I calculate all the hours for each project by month.
> To do this manually I can us a pivot table to get what I want at this
> current time. However I want to make a dynamic solution so I at any
> time can go in and see the summary without making a new pivottable.
> The dynamic pivottable shall show the summary for the current month.
>
> This might seem a little complex functionality, but if this really
> works, I will really bend myself in the dust for Excel and all the
> fantastic functionalities which is avalable.
> Anybody have some sort of tips, example or any guidens if this should
> be possible???
>
> Kind regards,
>
> Jon Haakon
>

"Jon Haakon Ariansen" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I really wounder how good Excel really is. I have for some years now
> worked with a simple worksheet where I register my hours of work for all
> the prosjects I participate in. In my workbook I have a sheet for all the
> months in a year where I have a date, project, activty and description
> column. The last sheet in workbook I have a summary where I calculate my
> salary based on each month and a total salary. This works fine.
>
> What I want is to expand the functionality by making a summary for each
> month where I calculate all the hours for each project by month.
> To do this manually I can us a pivot table to get what I want at this
> current time. However I want to make a dynamic solution so I at any time
> can go in and see the summary without making a new pivottable. The dynamic
> pivottable shall show the summary for the current month.
>
> This might seem a little complex functionality, but if this really works,
> I will really bend myself in the dust for Excel and all the fantastic
> functionalities which is avalable.
> Anybody have some sort of tips, example or any guidens if this should be
> possible???
>
> Kind regards,
>
> Jon Haakon
>

I don't see why this is a problem - PTs are designed to do just this thing.
Make your PT with Month as a page field. When you want to view it, just
select the desired month and refresh the table. No need to create a new PT.


--
Peter Aitken

Remove the crap from my email address before using.