Reference Isn't Valid ErrorRecently, when I opened an Excel file, an error message appeared, "Reference isn't valid". Show There was a chart behind the message, and I (incorrectly) assumed that was the problem. However, the error turned out to be a pivot table problem - the source data table was broken! I had to reformat the data as a named table, and that fixed things. See the details below, if you run into a similar problem.  Pivot Table Source Data ProblemIn my workbook, a pivot table was causing the problem. The pivot table's source data was a table that no longer existed in the workbook. As soon as I fixed the table, the error message stopped appearing Fixing the Problem - DetailsTo fix the problem I did these two things, and there are detailed steps below: -- 1) Located the pivot table's data source -- 2) Found and fixed the problem in the source data 1) Locate Pivot Table Data SourceTo find the data source of a pivot table, follow these steps:
The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. For my pivot table, it showed a table name - Table1 Usually, that table or range is highlighted in the background, but I could still see the pivot table in the background. So, I clicked the Cancel button, and went to the sheet where the data was stored. 2) Find Problem in Source DataWhen I checked the data sheet, there was no table - just a list on the worksheet. Nobody else uses the workbook, so how did the formatted Excel table go back to being just a list? Table Removed During Repair
Fix Problem in Source DataTo fix the Reference isn't valid error, I formatted the list as an Excel table again.
Next, I checked the Change PivotTable Data Source dialog box again, and the table was correctly highlighted in the background.
I clicked Cancel, and the error message didn't appear again, when I refreshed the pivot table.
新建帐户 无法处理你的请求此请求遇到了问题。我们会尽快将它修复。
Meta © 2022 Here are some problems that our users have asked and received explanations onNeed to auto-fit column header in pivot table drill down. So, when senior management clicks on a field in the pivot table to see the details, the formatting is already set. Solved by O. H. in 27 mins Not able to delete Pivot table using office interop plugin. Receiving the following error when trying to change pivot cache . "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field" Solved by Z. S. in 25 mins Hello, within a pivot table, I need to convert a field within row labels into a value (needs to calculate the sum of the input data), having already another selected field in the value column as sum. How can I have to values on X & Y axe in a pivot table diagramm? Please help, I'm lost! Solved by O. J. in 16 mins I need help creating a calculated field in my pivot table using data from the pivot table. Solved by S. Q. in 25 mins I keep receiving error message" the name 'monthname' already exists. click yes to use that version of the name, or click no to rename the version of 'monthname' you're moving or copying. Solved by O. S. in 20 mins |
