Forum Discussion
How can I use Pivot Tables to keep two worksheets of user information and add rows for new users?
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.
