Refresh Pivot Table in Order

[philmywallet]

Hi

I have some data which I bring into Excel from an external source (SQL). The import works fine, however as part of my spreadsheet I need to create a pivot table using some lookup fields (such as the month (which is a calculation of a date), which is why I cannot create the pivot table directly from the external source.

I have created my pivot table based on the table which is imported, however it appears that the pivot does not refresh on open (I have to manually refrsh the table each time). I think this is because the pivot table refreshes first (before the new data is loaded) and therefore there is nothing to refresh in the pivot.

I have tried changing the order of the tabs and changing the names to alphabetical, but it hasn't worked.

Any ideas on the refresh or if I can create a pivot field which is a month calculation of a date to use as a dimension?


Thanks



Phil

 

[Andrew Poulsom]

Welcome to MrExcel.

You can Group dates in a Pivot Table by Month. Here's a tutorial:

http://www.contextures.com/xlPivot07.html#Dates

 

[philmywallet]

Welcome to MrExcel.

You can Group dates in a Pivot Table by Month. Here's a tutorial:

http://www.contextures.com/xlPivot07.html#Dates

The only problem with this is that my date comes in as text, therefore i am unable to group. So I need to convert it as it comes in. Also I need to show the Month as a report filter and the date in the row lablels and I don't think I can do both at the same time. I think the refresh order might be the only way to go.

Thanks, but any other thoughts?


Phil