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?