Hi Daniel,

In session 7.03 I share some VBA code that will automatically refresh PivotTables when the focus is shifted from the sheet containing the PivotTable.

So, if you had your PivotTables and source data on the same sheet, preferably with the PivotTables to the right of your source data so they weren't in the way, when you enter your new data in the table and then move to your sheet containing the charts, the VBA would execute and refresh the PivotTables automatically.

The PivotTable will automatically re-sort upon refresh.

Let me know if you get stuck.

Mynda

Mynda

Thanks for the quick response.  

I'm still not quite achieving the result I after.  I'm sure it's going to be something simple but so far I haven't been able to resolve it. I apologise for the long description in advance as I try and explain the issue. I have attached a screen shot (I hope) to help you see how things are currently set up. 

My Pivot Table and the output of that which is the source data for the chart is on the same analysis sheet as you have described. The issue is that the pivot table is sorted highest to lowest based on the results for the previous weeks data the 18 June and the sort order has not updated to reflect the new data added dated 25 June.  

Of course I could just re sort the Pivot Table highest to lowest using the data for the 25th but I'm trying to avoid having to do this step every week given that there will be a number of similar charts for each measure within the report.

I'm not sure if the answer is in the pivot table or the formulas used in the chart source data table remembering that I'm looking at a second chart based on the sum of the last 4 weeks results.

Hoping you can help.

Thanks

Daniel 

Hi Daniel,

Thanks for sharing the image. That helps me properly understand what you're working with and how your PT is set up.

When you select a specific column to sort your data on you can't have this column automatically change each week to pick up the latest date and re-sort. Unless you write some VBA code to do that.

One way you could force this to happen is to add a column to your source data that classifies the dates into periods e.g. Week1, Week2, Week3, Week4 and 'other weeks' for those you're no longer interested in.

You could derive these classifications with a formula so they automatically update. You'd then use this new column in your column labels and sort by on the Week4 item. That way the PT never changes, only the data being pulled in updates as the formulas reclassify the dates.

I hope that makes sense. If you want some help with the formula please provide a file with some sample data and I'll build you an example.

Mynda

Mynda

Thank you for the advice.  The approach has delivered the outcome I was after with the added bonus of containing the size of the Pivot Tables as the source data grows.

Thanks again

Daniel

Great. I'm glad that helped 🙂