Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Error in Linked PivotTable Value.
Written by Allen Wyatt (last updated April 21, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003
Adam has two workbooks; call them A and B. In workbook A he has a link to a value in a PivotTable that is in workbook B. When he opens workbook A and workbook B is not open, Adam gets a #REF! error for the link. He wonders if there is any way to avoid getting the error when linking to a PivotTable value in a workbook that is not open.
There are a couple of ways you can approach this problem. Both methods involve understanding how Excel references the PivotTable value in workbook A. When you create a link to the value and both workbook A and workbook B are open, the reference will look something like this:
=GETPIVOTDATA("TotalValue",'C:\XLDocs\[MyData.xls]PTable'!$H$15,"EName","Rac")
One way to handle the problem is to envelope the reference within an IF statement, in this manner:
=IF(ISERROR(=GETPIVOTDATA("TotalValue",'C:\XLDocs\[MyData.xls]PTable'! $H$15,"EName","Rac")),"Make sure Workbook B is Open", =GETPIVOTDATA( "TotalValue",'C:\XLDocs\[MyData.xls]PTable'!$H$15,"EName","Rac"))
The formula checks the result of the GETPIVOTDATA function, and if it returns an error value (like when workbook B is not open), it displays a message. Only if there is no error value will the value in workbook B be fetched.
Another way is to modify the original reference so that the GETPIVOTDATA function is not being used. (It is this particular function that is generating the error when workbook B is not open.) Here's the way you should redo the reference so that the value is referenced directly instead of through a function:
='C:\MyWork\XLDocs\[MyData.xls]PTable'!$H$15
When the reference is rewritten in this manner, the error condition isn't returned.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10649) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Error in Linked PivotTable Value.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
PivotTables are a great way to process huge amounts of data and make sense of that data. If you have a number of ...
Discover MoreChanging the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...
Discover MoreWonder what happened to the data behind a PivotTable? It could be in a number of places, and tracking it down could be a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments