I have 4 columns.
The headers are Release, ResourceType, Month1, Month2... Month12. The month column captures the effort required (numerical values) by the resourcetype.
Release has entries like - R113, R213,R313 and R413
ResourceType has entries like say - Type1, Type2, type3, type 4 and repeating in this column.

What I am trying to achieve is - I want to have 4 tables (one for each release R113,R213..) and each table will capture the resource type and the effort by Month1, Month2....Month12. I would like to have Months in column, Resource type in rows and the effort values in the data matrix.
Also, I would like to automate this. I mean if the values change in the original table, the above created table should automatically get updated.

Could you please help?
SG

Hi There,

It's possible through either writing a VBA routine or a pivot table (if you are using Excel 2007 or later). I can help you with the VBA routine but the latter is easier. Here is how you achieve this using VBA.
1 Put data into a table (excel range).
2 Create a pivot table with the layout you wish.
3 Ensure you have the RELEASE in the page field.
4 Go to the Options tab -- PivotTable group -- Options -- Show Report filter pages.
5 Once you follow the above process you will see that Excel creates several pivot tables (one per RELEASE) and renames the sheet with the name of the RELEASE.

This is quick and easy. If you need to refresh the data in all pivot tables go to Options -- Data -- Refresh All.

Hope it was useful to you!

Thank You,
Prashant