Results 1 to 2 of 2

Thread: Auto Update Pivot Table Help

  1. #1
    Neophyte golfpro2301's Avatar
    Join Date
    Jul 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Auto Update Pivot Table Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Neophyte golfpro2301's Avatar
    Join Date
    Jul 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    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=1fy...56lj4wlOfZYGSi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •