Forum Discussion
How can I use Pivot Tables to keep two worksheets of user information and add rows for new users?
I have a large spreadsheet that is used by my organization to keep track of users and all types of information about them including Roles, account requests, corporate training, system access, industry certs, etc. I want to break the spreadsheet up into two spreadsheets (same workbook) that reference the same users but breaks up the different information based on the auditor (Auditor A reviews account requests and corporate training, while Auditor B reviews system access and industry certs.
I've created a pivot table of the first two columns of the Master and copied the contents into the AuditorB spreadsheet, but when a new row is added for a new user to the source it doesn't create a new row for the AuditorB spreadsheet. I've tried creating tables on both spreadsheets but it only updates the contents of column A and B, leaving the rest of the data in the AuditorB spreadsheet as static. Can someone direct me to how to get this to work? I'll try uploading a dummy spreadsheet of the format of the spreadsheet that I work with.
If there's a different more efficient way to achieve my goal I am open to it; I don't have to use Pivot Tables if there's a better way. Thanks in advance!
3 Replies
Unless I'm missing something here, I think you'd be making a mistake. I say that because what I'm hearing is taking a single database and (essentially) duplicating it.....making for unnecessary redundancy, a need to maintain two duplicate (or highly overlapping) sets of data, increased opportunity for inconsistency....
So unless there's some compelling reason why Auditor A and Auditor B can't be working from the same, single database, where any change is made once for both users, and so on, you and your organization would be better served by keeping it all as a single database. Excel is very adept at extracting data (FILTER, etc) for different users, including what A needs to see in one "dashboard." what B needs to see in another.
The spreadsheet that we work from has over 50 columns, and a subset of them are only relevant to Auditor A and another subset is only relevant to Auditor B so we wanted to see about breaking it into 2 spreadsheets; the uploaded spreadsheet is extremely simplified. We would also cut the columns that are only relevant to Auditor B onto the new spreadsheet, but we want to make sure that the users are always the same. The problem that I'm running into is when I make the pivot table out of the first two columns (name and role), when I update the source with a new row for a user the pivot table on the second slide updates, but a new row is not created and thus the data that was on the same row as the existing users doesn't move down; they stay in the same spot when a new user is added to the first two columns of the pivot table. I've tried making both spreadsheets as tables to keep all data associated, but it doesn't seem to work.
Here is the link.
https://teamsassi-my.sharepoint.com/:x:/p/joseph_sutton/EXZxlccSSkxDskqmTd1OACUBZNyMh4e3Nc2MbtU32awSSw
