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

Osiris1316

New Member
Joined
Sep 23, 2019
Messages
1
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]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top