Pivot table field name is not valid error

I have an Excel 2010 workbook with a main table on one sheet and several Pivot Tables on other sheets. All of the columns have headings, all of the headings contain only text, there are no hidden columns. The pivot tables were working fine until I moved a column in the table (from column F to B). The column contains a formula and I confirmed that it is calculating correctly. =INDEX(EMPLOYEES!B:B,MATCH(DATABASE!$E2,EMPLOYEES!A:A,0))

 When I try to use an existing Pivot table or create a new one, I get the error message "The PivotTable field name is not valid. To create a pivot table 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."

All the posted answers addressing this error message state the problem is unlabeled columns or values other than text in Row 1.
Since this is not the case, what are the other causes that would generate this error message? This is a large spreadsheet with many formulas and I don't want to start over.
 

Question Info


Last updated August 12, 2019 Views 144,981 Applies to:
Answer
Answer

And here I thought I was the only one with this problem.  As with any  other problem the error message means something to someone, if you know where to start.  The comments made by AussieJim made me re-think my errors, and I figured it out, for my scenario.

 

My problem was very simple, but easy to overlook.  I used a range and not a Named Range for my PivotTable.  So, when I was done filtering the PT to make my chart everything was great, chart did the trick.  But, when I closed and re-opened the report I got the error message in question.  I ended up changing my PT source from the range I originally selected to creating a Named Range (I did mine in VBA but you can do it in the Name Manager) and whalla, error message gone!

 

Look at where you are pulling your source data from by selecting your Pivot Table, then go to PivotTable Tools > Change Data Source to check the data source, does it pull from a differnent location/cell than you thought?

 

Good luck!

SD

36 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer
I had the same problem with some Pivot Tables after upgrading from Excel 2003 to Excel 2010.  The Pivot Table used a name to identify the data sourceused in the Pivot Table - this had worked fine in Office 2003.  After many hours trying to track down the problem I found that Excel 2010 had created 3 identical names, each with different ranges.  Excel 2003 had just the 1 (correct) name and so I deleted the 2 superfluous names in Excel 2010 and this instantly fixed the problem.  In Excel 2010 go to PivotTable Tools > Change Data Source to check the data source used in your PivotTable and go to Formulas > Names Manager to check your names.  Hope this will be of some use - good luck!

87 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.