Pivot table relationships between source table data

Hi,

I am trying to create a pivot table that will report on data from two table within my workbook.

TABLE 1: Folders
Lists folders and subfolder pathway where applicable, Group ID (for the permission group that has access to that folder/subfolder).

TABLE 2: Permission Groups
Lists the Group ID, Group Prefix, Group Suffix and Group Name (concatenate of prefix and suffix).

I want my pivot table to list the Group Names under the Folder names so I created a relationship between the GROUP ID of both tables. However when I then drag the desired field into my Pivot table it shows ALL group names under each folder irrespective of the group id.

I watched this video before creating: https://www.youtube.com/watch?v=6G16cSmi44M

Any help much appreciated.

LizzyJ

I have attached a version that removes all the names etc. but is the exact same in every other way.

For the pivot table ideally I would like to filter by root folder and subfolder (where applicable) and then see the Permissions Groups (instead of the Group ID which means nothing to anyone except me).

Ultimately I would like to be able to show the Usernames as well.

I have created what I thought were the correct relationships between Group IDs to make this work but it still displays ALL the Permissions Groups under each root folder/subfolder despite only some of the Group IDs begin listed for that folder.

Any help much appreciated- if any of what I have said doesn't make sense let me know and I can try and reword.

thanks
LizzyJ

Where do I do that?

This is probably very basic but I haven't used relationships in Excel before.

Thanks
LizzyJ