Pivot table auto refresh, live dashboard update

[JackDanIce]

Hi,

I have a data table turned into a pivot table. I want to use this pivot table to drive a dashboard (i.e. slicers, timelines other interaction)

A user needs to be able to change variables which updates data table which in turn updates pivot table and dashboard

E.g. a variable that affects a growth rate for values across a given row in the data table​

I'm using Excel 365 and file saved to OneDrive account. The dashboard needs to be shared to multiple users so will be published online.

I could use Worksheet events to trigger pivot table refresh, but I'm unsure/unclear if this is:

- An ideal way to make an interactive dashboard and shared to multiple users
- If it's being hosted online for multiple users to access and interact, will using macros need all users to accept the warning message to enable macro events

Do not have access to PowerBI or other Power apps, prefer to avoid VBA.

Looking for advice, suggestions, comments please.

TIA,
Jack

 

[sandy666]

Connections - (PivotTable) Properties - set refresh as you want

 

[JackDanIce]

Thanks for suggestion @sandy666, however,

PivotTable Analyze ->Data -> Refresh but Connection Properties is greyed out.

Data table is "static" (apart from rows with formulae) from which I have created the pivot table.

 

[sandy666]

you need to find something like this

Point 3: you need to select proper table

 

[JackDanIce]

Used Insert Table to define the data table, used it to create a pivot table (slightly manipulation through power query) and the pivot table now does update when I change a variable to the data table.

Thank you for the guidance and very helpful annotated screenshot! Much appreciated

 

[sandy666]

You are welcome

Query Table or Pivot Table can be refreshed the same way
If Pivot Table was created from Query Table enough to refresh Pivot Table

Have a nice day

 

[JackDanIce]

Thanks and you too!