Forum Discussion

Francisco77's avatar
Francisco77
Copper Contributor
Aug 08, 2024

Refresh pivot table

Hi Community,

 

Is it possible to make it so that if I filter a database, a pivot table (originating from this database) takes the filtered data, that is, that it updates automatically taking only the visible data from the database?.

 

Regards,

 

Francisco

excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 09, 2024

    Francisco77 

    Add a column to the data table. Let's call it "Visible". Enter the following formula in that column:

    =SUBTOTAL(3,[@Ref])

     

    Replace Ref with the name of any of the other columns in your table, though one that always contains a value (text or number). A sequence number, customer ID or whatever.

     

    This will initially fill the 'Visible' column with 1's, but when you start filtering out rows the invisible rows will have 0 (zero) in that column.

     

    Now build your pt and drag the 'Visible' column into the Filters area and select only 1. That is, unselect  the zero option. 

     

    Now, the pt is set show only results (upon a refresh) for data that is visible in the data base. Not fully automatic as it requires a refresh, but that's how it is with pivot tables. I guess you could automate a refresh with VBA but I can't help you with that.

Resources