Splitting an activity-tracking sheet into multiple category sheets

I have an activity-tracking spreadsheet which includes a collated list of all activity and separate sheets showing only the activity for each specific category (the attached workbook gives you a small snapshot of the type of data I have an the split I would like to achieve).

I would really like the people going into the workbook to only have to update one list - the category list or the long list, and have the list which they have not completed automatically update (so if they fill in the long list the category list automatically updates or vice versa).

I am being a bit dim today and have tried to think through various scenarios of lookups, filtering and pivot tables, but can't figure out how to do this when there are multiple sheets (though it is probably really simple).

Does any one have any advice on how to set this up / what formulae to use?

Thanks

G

Allow the users to update the Long list. Create a Pivot with category in the row, activity in the column and count of users in E. Then in the Pivot if u double click on the #of users against blue u ll get the details of blue in another sheet.

Thanks Vinoth.

I learnt a new trick with Pivot tables and it split the categories out, but it wont auto-update.

As you can see the attached - I created the pivot table using a data range which went beyond the existing static table so it would pick up additional rows of data. However, even when I added the extra rows and updated the pivot table, the new 'red' spreadsheet did not update.

Ideally I would like this to automatically update without people even having to update pivot tables as the users will only have basic excel skills. is there a way of doing this this route?

Thanks

G