Help with converting 2007 pivot to work on 2003

**mztexas** · 07-13-2010, 05:55 PM

Hello,

I created a report in Excel 2007 with pivot tables. However, I just found out that the people that will be updating it are on 2003. As a result, none of my pivot tables will work. Can someone please take a look at the attached spreadsheet and give me some guidance on how I can get this to work in 2003? I would like for the pivot tables to update with the added data when refreshed but not sure how to do that either in 2003.

**oldchippy** · 07-13-2010, 06:03 PM

Hi,

Can't you just save as 97-2003 format?

**mztexas** · 07-13-2010, 08:14 PM

No, when you save down, it does not allow the pivot tables to update. Pivot tables in 2007 are not backward compatible. Thanks for the suggestion though.

**oldchippy** · 07-14-2010, 03:43 AM

Hi,

Does this help?

http://www.brighthub.com/computing/w...les/24484.aspx

**DonkeyOte** · 07-14-2010, 04:55 AM

Point being you need to create the Pivots in Compatibility Mode ... if they were created in XL2007 you can not convert them retrospectively.

Originally Posted by mztexas

I would like for the pivot tables to update with the added data when refreshed but not sure how to do that either in 2003.

Use a Dynamic Named Range as the source for the Pivot (see link in sig. for more info) and/or use an appropriate VBA event to refresh the Pivot cache as when that data is altered.

**mztexas** · 07-14-2010, 09:32 AM

I am not sure how to write the code for a dynamic named range. Can you help me out? The file is attached to the first post.

**DonkeyOte** · 07-14-2010, 09:53 AM

Creating the Dynamic Named Range itself is simple enough, eg:

Please Login or Register  to view this content.

When creating the PT you would enter _PTData as source
(if you continue to use existing 2007 versions you can go to Pivot Table Options bar on Ribbon [when PT active] and click Change Data Source)

In theory you can use a Change event on the Application Data object to refresh the PT Cache(s) as and when that data is altered:

Please Login or Register  to view this content.

but you may need to be careful regards positioning of the Pivots

In reality - I would suggest creating a back up and running some tests to see if you can get what you want.