Updating Excel Table to coincide with PivotTables

[Byebye14]

Hello all!!

I have a question that hopefully somebody can provide the answer to.

Scenario: See attached image. I have an excel file with three sheets: Period Performance, Completed Early and Tables. In the Tables sheet, I have two pivot tables: Period Performance Pivot and Completed Early Pivot. They pull data from their respective sheets. Also in the Tables sheet is a Summary Table. This Summary Table uses some of the data from both pivot tables to formulate an overall performance. See image.

Goal: I want to make my Summary Table update to reflect the Dept rows from the pivot tables. Ex., If you refreshed either pivot table and Dept C was added as a row then the Summary Table should automatically update to add Dept C. Vic Versa, if the pivots were refreshed and only row Dept A was found in the pivot tables then the Summary Table should only show Dept A.

Clarification: The data from the two data sheets is not really relevant. Both Pivot Tables work perfectly and when refreshed show the appropriate data. I simply need the Summary Table to update when the Pivot Tables are refreshed. How do I do this??

Any help is beneficial!!! Thank you and let me know if I can clarify anything.

[ChemistB]

Can you upload the workbook or a simple example (sans confidential information) so we can see how you currently have your formulas set up?
Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Byebye14]

Hi! Thank you for your reply. Attached you will find a sample workbook for my problem. Thank you!

[Mrneski]

Hi, i'm no expert but I have had a little play with your file for you to review.

Summary of actions

• Edited GETPIVOT to reference column M
• Added IF & IFERROR to allow for Grand Total row & blank cells
• Added conditional formatting to restore original aesthetics

Let me know if this helped

[Byebye14]

Hey Mrneski! See new attached image in original post

Thank you for the reply. Your file works and is exactly what I wanted. However, I have updated my actual excel format so that the two pivot tables are merged together (completed & planned) show in one pivot table. This is done by first filtering the columns to show "Completed Early" and "Planned" tasks. Then each of those is filtered by "Percent_Complete".

I still want the same summary table you worked on.

Issue: I am having trouble manipulating the

Please Login or Register  to view this content.

code. I now need to get the same data but do it by referencing two column filters. Attached you will see the set up with the two filters. Question: How do I get to the 12, 8 and 32 shown in the picture? Thanks!!!!

Snippt.PNG

[Mrneski]

Hi there, sorry for my late response, I saw your reply earlier in the week but have been very preoccupied with the day job & didn't have the time to give this the focus I should.

Firstly, is it possible to get a copy of the revised sheet? Being 100% self-taught, a written formula can be difficult for me to work with whereas if I can physically edit the document, I find it much easier.
Secondly, I meant to ask in my initial response, what is the purpose of the pivot, only to update the table? If so, would there be a benefit to removing the pivot rather than using the current method?

I look forward to hearing back & hope to make time to find a solution over the weekend.

Regards,

[Byebye14]

Mrneski,

I ended up getting it working thanks to your earlier reply! Thank you for the help!