I am trying to create a pivot from a range but keep getting the error Data source reference is not valid
I have Googled this and tried all of the following.
- Select from Table instead from range.
- Open the Excel spreadsheet click on the view tab and select new window. Save the spreadsheet with a different name for the new window.
- Click on HOME> PREPARE> INSPECT DOCUMENT> Removed all unneccesary references and xml forms.
I have labels on all the columns and all the cells are populated.
I am using Office 2010 on Win7 and the file is in .xlsx format
Best Answer
I found the reason the pivot tables didn't work was because of the file name. The reports are generated by a report automation system and the name contains square brackets
[]
that Excel doesn't like.I removed the square brackets totally by chance and found the pivots are working.
I also tried to save the file in the
Save As
option with square brackets but like I said, Excel moans about it and doesn't allow you.