I'm having some trouble creating a mechanism generates a matrix I can update and tracks value pairings in such a way that new or changed data doesn't corrupt the matrix relationships.
Starting with:
Group sheet and Role sheet:
Goal:
Generated matrix:
Change-tracking:
Matrix filled:
Group AB added, Group B removed, RoleA renamed, RoleBC added:
Matrix refreshed to update header rows and columns, preserving relationships:
Non-requirements:
The matrix does not need to preserve sorting or filtering on the Group or Role sheets.
The matrix does not need to 'remember' data associated with group/role pairs that have been deleted from the source sheets.
Also:
There is an ID column on the Group and Role sheets.
I'm not sure how best to achieve this. I can generate a pivot table, but I can't seem to update the values in the generated table (PT seems to be designed for data summary rather than data entry). I haven't been able to figure out how to make a query that uses the Groups table and then adds the Roles table as additional column headers, and then properly tracks manually-entered values. I have an inkling creating a data relationship might help, but unsure how to apply that.
Is there a 'best practice' or tool I should be using to achieve this? Thanks for any advice!
Starting with:
Group sheet and Role sheet:
Goal:
Generated matrix:
Change-tracking:
Matrix filled:
Group AB added, Group B removed, RoleA renamed, RoleBC added:
Matrix refreshed to update header rows and columns, preserving relationships:
Non-requirements:
The matrix does not need to preserve sorting or filtering on the Group or Role sheets.
The matrix does not need to 'remember' data associated with group/role pairs that have been deleted from the source sheets.
Also:
There is an ID column on the Group and Role sheets.
I'm not sure how best to achieve this. I can generate a pivot table, but I can't seem to update the values in the generated table (PT seems to be designed for data summary rather than data entry). I haven't been able to figure out how to make a query that uses the Groups table and then adds the Roles table as additional column headers, and then properly tracks manually-entered values. I have an inkling creating a data relationship might help, but unsure how to apply that.
Is there a 'best practice' or tool I should be using to achieve this? Thanks for any advice!