Child Sheet updating Master Sheet (Pivot Table / Power Query)

[Osiris1316]

Hi everyone,

I'm going to post a link to a question I asked in the Excel subreddit. I will also paste the original question here, but note that a discussion ensued which brought me here. Please read the comments to see where myself and u/gravy_boot managed to get to, before we got stuck.

OP:

[FONT=&quot]"Hi folks,[/FONT][FONT=&quot]I am creating a workbook which I want to include:[/FONT]

• Master Sheet (data storage -> rows = personnel, columns = personnel identifiers)
  
• Multiple sheets with pivot tables generated from the Master Sheet

[FONT=&quot]Ask 1 - Is this possible and how can I do it?[/FONT]
[FONT=&quot]I would like the pivot tables to auto-populate rows from the master sheet based upon the identifiers listed in various columns. For example:[/FONT]

• John has 3 years of experience, while others have 2 or less.
  
• I want one sheet to list all the people with 2 or less years of experience using a pivot table (unless there's a better option)
  
• I want the sheet listing people with 2 years of experience or less to automatically remove and add rows based on the indicators in the master sheet.
  
• So, let's say that Jenny has 2 years of experience, but in a few months that changes to 3, I would like the pivot table in the sheet listing people with <2 years of experience to remove Jenny's row.
  
• As well, let's say Bobby get's added to the master sheet with 0 years of experience on day 1, I would like the pivot table to automatically add his row to the sheet listing people with <2 years of experience.

[FONT=&quot]Ask 2 - Can Excel do this?[/FONT]
[FONT=&quot]I would also like the person managing the workbook to be able to both change data in the master and pivot tables and for the data to be updated in the person's row back and forth. For example:[/FONT]

• Bobby has been with us for a year.
  
• I want to be able to change his "years of experience" cell (Bobby's row, Years of Experience column) in the sheet with the pivot table.
  
• I want this to then change the respective cell associated with Bobby and years of experience in the Master sheet.

[FONT=&quot]If this is not possible, that's OK. Just wondering."

u/gravy_boot suggested that a power query using two child sheets (one for complete and pending) would provide functionality that enables the user to update either the Parent sheet, or the Pending Child Sheet and ensure the other updates as well. I'm wondering if this is the most efficient solution or of there is another option.

Thanks![/FONT]