Refreshing Pivot tables *after* data table upon opening workbook

[Ludwig]

I have a spreadsheet that has an external data source set to "Refresh data when opening the file". I also have multiple pivot tables using that data which have "Refresh data when opening the file" marked.

Is there a non-VBA way I get the pivot tables to do their auto-refresh on open only *AFTER* the external data source has been updated when opening the workbook?

I know I can achieve this using VBA, but it would be nicer to have Excel do the refreshes in the correct order without being forced to make it a macro-enabled workbook.

Thanks.

 

[Ludwig]

After much experimenting, I have found a way to do this. Posted here what seems to be the answer in case anyone else is looking for "how"....

Open spreadsheet & go to ribbon tab "Data", select "Queries & Connections", then right-click on the relevant Query showing on the right and selected "Properties".

Under Usage tab, found "Enable background refresh" was ticked - so I unticked it. Pivot tables now seem to refresh *after* the latest data connection has refreshed.

 

[Alex Blakenburg]

You are allowed to mark your own post as a solution when you provide the details as you have done.
I learnt about it from Celia Alves and she covers it here.

https://solveandexcel.ca/2020/09/11/enable-background-refresh-property-in-power-query-in-excel/

 

[Ludwig]

Thanks for your post Alex. The link perfectly explains why & what I needed to do.
Pity I didn't know about the "background refresh" property to be able to successfully search for the solution [& hence find that link]. I only found the property existed by accident after getting so annoyed with the issue. My initial 'reminder alert' was to put a formula in a cell to check the consistency between one key pivot table and the imported table data (by checking that both transaction count & sum of a $ column match between the two) and then reflecting that value on the top of every other affected worksheet so I didn't miss it!

Hopefully this post might help someone else in the future.