Reference is not valid when refreshing Pivot tables

[e242xtcc]

I am using Excel 2007. i have a pivot table in one workbook using a data table from another workbook. when i just have the pivot table workbook open and i refresh the pivots i get the reference is not valid error. i can only refresh the pivots if the other workbook is open. how can i correct this. i never had this issue in excel 2003.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Does your reference to the PivotTable DataSource include the file path?

You can check the current reference by clicking on the PivotTable, then click the Options Tab in the Ribbon, then Change Data Source.

 

[e242xtcc]

The file path looks like it should, and how it worked when it refreshed with no issues in v.2003. I tried my local drive as well same results. Currently both workbooks reside on a network drive, shared folder. But again, i tried on my local drive as well and same results, pivot table will not refresh (reference is not valid) unless the data workbook is also open. very frustrating since i had no issues using v.2003.
file path looks like this:
'y:\Cost Control Spreadsheet\CEVA Ground 2012 Cost Control Data v1.xlsx'ccdata

 

[RoryA]

A table is effectively a dynamic named range, so it will not work if the data workbook is closed. You would need a fixed reference if you want that ability.

 

[e242xtcc]

So this is different then how the "list" range worked in 2003? I need a table workbook since records are added every day and these records need refreshed to the pivot table workbook...how do work around this issue?

i should tell you i am OK at excel, but not an expert.

I have many more workbooks to re-develop in v.2007 that work this very same way. one workbook contains the data (updated everyday) and 1 workbook with all the pivot tables that summarize this data(used everyday). all of which will reside on a network drive and will have multiple users opening, using and saving from all over our company

 

[RoryA]

Yes it is different - there were no table references for formulas pre 2007, for instance.

I would suggest that the simplest thing is to open the source workbook when refreshing the data. If you cannot do that, try using MSQuery to get the data from the whole sheet in the other workbook as the data for the pivot.

 

[e242xtcc]

thank you for your help. i removed the dynamic range (now that i understand what that means) and changed the source range to a more static range and it works fine now.

Now if i could just understand why my calculated fields in v.2007 don't work the way they did in v.2003...grrrrrrr

 

[TintinRuby]

Hi and Welcome to the Board,

Does your reference to the PivotTable DataSource include the file path?

You can check the current reference by clicking on the PivotTable, then click the Options Tab in the Ribbon, then Change Data Source.

SPOT ON! This response easily solved my problem. Thank you!!!