Pivot Tables in Calendar

LTAJSR
LTAJSR used Ask the Experts™
on
I am using data (with on-going changes)  to create a quarterly calendar that goes out 5 years (attached a sample 8 of 20 tables) and need your help with a few elements:
1.  are pivot tables best way to present data as shown in the attached file.  If not, can you suggest another option.  Currently each calendar consists of 20 Pivot Tables/Sheet (1/quarter JAS'10, OND'10 etc)
2.  When using pivot tables, how can I get the tables to sort automatically in column Scost 4 when data is updates are made.  In the attached example you will notice, Sub Division has to be moved up to the source section prior to sorting from greatest to least, then needs can be placed back in table.   I have to do this for each pivot table 20/sheet.  Is there an automated way to handle this sorting when new data is updated in the Raw Data Tab.
3.  When I copy and paste new data into the Raw Data section, I save the file, close it and re-open but I don't see the changes carry over to the pivot tables.   Is there another step I should take?
4.  Formatting the cells:  
a)  Is there a way to have subdivision first instead of tasks but not have it group (ie. Laundry groups the clothes and clothes 2)?  I want table to sort tasks by Scost 4  from highest to least.
b) I would like to automate the all cells to have a thin boarder but a thick boarder along the outer boarder.  you will notice JAS 10 will be a smaller table - is there a way to have it automatically be formatted to look like the longest table?
c) what is the best way to get from Pivot Table to Presentation.  Currently I am Copy Sheet then copy and paste values and then switch the sub division with the tasks  and format.  Is there a way that I can avoid this step by getting to this look in the Pivot Table tab - so when I refresh data it always will have the same format look as in the Presentation Tab?

5. Is there a way to make a change all the pivot tables at the same time ie. Grouping Importance 1-3 together vs. individually doing this? Is there also a way to avoid the tables to overlap when doing this?

6.  When new data is updated, the tables sometimes get to long and overlap the one at the bottom.  Is there a way to prompt inserting of cells to compensate for new data and to avoid overlapping?


test.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,
I agree with zorvek, you might get faster progress with simpler questions.
But heres a start. :-)
Please find attached how I would structure you problem. I have added a column to grab
the year. Such column can be used to structure the data into something similar to your presentation layer.

Short (4a+c): I would make ONE pivottable, create a new "Pivot Table Style" to match your layout desires. Thereby you do not need to make any changes after having applied new data.

1+2+4b) I would not recommend to utilize that many pivot tables in the way you have it here. Structure data and live with slightly less attractive layout. In my example you loose the "row header" of each section but instead of 20 tables you only have one. I know you have a sorting issue which I do not address and I might have over-simplified the suggestion, but I attempted to direct you into a better utilisation of pivottables, and the sorting you want can not be made without the multiple pivottable solution. Could you live with another identification of the largest values, e.g. conditional formatting?

3) You need to extend your data range to grab a larger dataset. Select a cell in the pivottable, Select "Pivottable Tools" and "Change data source". To update a pivot table, right-click a cell in the pivot table and refresh, but it will only work correctly if your dataset is the same or smaller than your current selection.

5) Make ONE table, if you need sorting grouping 1-3 you can group the elements in the pivot table, or create another help column with some =IF........statements in the cells using the priority column data. As shown in my example with the "year" column.

Cheers
cslarsen


EE-test-pivot.xlsx

Author

Commented:
I like your idea and i want to work with it...as it is much more efficient.   Issue I have is each quarter has different tasks and subdivisions.  is it still possible to use your suggestion?
Commented:
Hi
The "column header" (leftmost list of text) will have the total consolidated list of all quarters.
It will not be possible to make a list for each quarter.

I would also think that the readability of your report would drop significantly if the data is not the same on each line across quarters.

Cheers
cslarsen

Author

Commented:
The solution was helpful to understand I could not do what I was wanting to accomplish.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial