Restoring Pivot Table Source Data

[jane serky]

Hi

I have a file with multiple pivot tables and data source sheets. The data sources are dynamic named ranges which the pivot tables use. To reduce the file size, I delete the source data sheets and the named ranges, after updating the pivot tables.

My problem is that I need to re-instate the data sheets each month so that I can update the pivot tables. When I copy the source data back to the file and recreate the named range and try to refresh the pivot table, I get the error 'reference is not valid'. When I check the range name, it highlights the correct raw data so that is not the problem.

What am I missing? Is there a better way to do this?

Thanks

Forgot to mention that if I rename the named range for the source data, update all of the pivot tables source to the new named range and refresh, it all works OK. I can even rename the source data back to the original range name and it still works OK. This is a lot of work though and I thought should have worked with the original name.

[noboffinme]

Hi jane serky,

Are you removing the source data so you can e-mail your data & want to keep the size down?

If so, you can create a copy of just the pivot into a new workbook & send that.

Pivots source data is really touchy as you've discovered so it's best not to mess too much with the source.

If there's anything even slightly different it will error or remove fields.

Hope this helps

[jane serky]

Am posting how I solved this as it may help someone else. I was building a dashboard using Excel 2007. It had ~ 6 months data and performance was extremely slow (it was unusable). I was trying to reduce the size by deleting the raw data but this was causing problems when I had to 're-instate' the data with an extra month's data for reporting each month. It was just impractical and prone to error.

I solved this by switching the dashboard to PowerPivot (Excel 2010). I now have many years worth of data with no impact on performance.

I hope this helps other people in this situation.

Thanks