auto pivot table update issues

[golfpro2301]

Hello All

Having an issue with auto updating a pivot table.

In my workbook I have three tab.

Tab 1 - dashboard (where I want pivot tables
Tab 2 - Main data
Tab 3 - Consolidated Data for charting purposes

Tab 2 is where I input everything. It is a grid with employee names on left and work orders across top. Each employee/work order is assigned a budget of hours and how many actual hours it takes to complete. I am tracking employee and work order efficiency of the course of a project. To create the bar chart based on work order efficiency and to color code it based on value I pulled this info only into Tab 3 using the below formula: (Note - there are 40 total work orders. "Employee Data & Work Order Data is Tab 2

Work Order #XX ='Employee Data & Work Order Data'!FJ54
Work Order # XX ='Employee Data & Work Order Data'!CX54

I then split this out on values based on their percentages. In my pivot table on dashboard I want it to show top 5 and bottom 5 efficient work orders. I have table setup correctly and can manually update it. Looking online I found the below Macro and input it

thisworkbook.refreshALL

Any changes I make in Tab 2 gets copied over to tab 3 which is where pivot table is based off of. When values change the pivot table doesnt automatically update. However if I remove the formulas in the Tab 3 cells and manually type in a value the pivot table automatically updates

My question is how do I get a pivot table to update when it is based on cells that have a formula.



***Side note***
On Tab 3 there are the 40 work orders and the 40 correlations efficiency percentages based on information from Tab 2. Work order column A, Percentages column B, Column C is percentages under 95%, Column D is percentages 95-105, Column E is over 105. I have it setup that anytime a value in column B changes it places the value in correct column. I did this so chart is sort by color. However I want the chart to be sorted by value automatically. Right now I have to manual sort the data to keep all the good, average, bad percentages together. I am sure there is a macro to auto sort this page anytime there is a change.

Thanks for the help

 

[golfpro2301]

Bump.


Sorry all. I really need to figure this out. Not sure why I am able to do it in a new document even with formulas but not in my file. Maybe because my file below I am trying to do the VBA from cells that are copied from cells that have a formula that are copied from cells with a formula. Any help would be greatly appreciated.




https://drive.google.com/open?id=1fyCO06KemJn1KXSlG156lj4wlOfZYGSi