Dynamic pivot table

[Jon Haakon Ariansen]

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

 

[Carim]

Hello Jon,

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

HTH
Cheers
Carim

 

[Roger Govier]

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

 

[Peter Aitken]

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.