Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Dynamic pivot table
Hello Jon,
Take a look at Debra's brilliant work : http://www.contextures.com/xlPivot01.html HTH Cheers Carim |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Dynamic pivot table
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. InsertNameDefine 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Dynamic pivot table
"Jon Haakon Ariansen" wrote in message
... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic pivot table linked to dynamic excel spreadsheets | Excel Worksheet Functions | |||
Pivot table from dynamic refreshed pivot table | Excel Worksheet Functions | |||
Dynamic pivot table | Excel Discussion (Misc queries) | |||
Pivot table dynamic Filter | Excel Worksheet Functions | |||
Pivot Table & Dynamic data | Excel Programming |