Data source reference is not valid in pivot table wizard

  • Thread starter Thread starter Oliver Marshall
  • Start date Start date
O

Oliver Marshall

Hi,

I'm trying to create a pivot table in Excel 2007, something I have
done a thousand times in 2003. However, no matter what I do, the
wizard just tells me that the data source reference is not valid.

I have a table with ITEM NAME, COMPANY NAME, QNT, PRICE, LINE PRICE
etc as columns. Each column contains only one data type (ie either a
number, or a currency, or a date etc) and the whole table is set as a
table with the name SALES.

Whether I type the name of the table, or let the wizard select it, or
type a range of cells, or drag using the selector in the wizard, i
just says the same thing.

Even tables from the 2003 sheets which work fine in pivot tables dont
work in 2007 !!!

Any idea why ?

Olly
 
Hi Olly

I have had no difficulty running any of my Xl2003 Pivots in XL2007.
Are you sure you don't have any hidden un-named columns?
Try again with Data>Insert table>give it a different name>then with cursor
within the table>Data>Options>Create Pivot Table.

If that doesn't work, you can email me a copy of the workbook and I will see
if I can see what the problem is.
To mail direct send to
roger at technology4u dot co dot uk
Do the obvious with dot and at
 
Weirdest thing. I saved it with a new file name (sales1.xlsx rather
than sales.xlsx) and it worked just fine !!

How odd.


Ta anyway
 
Maybe it has to do when you are opening the excel file from a website.
It's opening the file from a temporary folder.

When I save it somewhere with the same filename it just works!

Had the same problem.
 
Olly,

I have had this issue a few times and have found that the file source name has been my problem.
I have been downloading out of a main data warehouse and trying to create a pivot directly off the download.
I need to save under a new file name in xlsx. to enable the pivot to function.

Frustrating really.

hope this helps.

StevieG
 
Weirdest thing. I saved it with a new file name (sales1.xlsx rather
than sales.xlsx) and it worked just fine !!

How odd.


Ta anyway
Click to expand...

I had the same problem - until I saved with a new name, it wouldn't work. Thanks for the tip!
 
I had the same issue. The program that I used to export the original data puts it i n web page format, and uses underscores on both the sheet name and file name. I think one of those two reasons causes the error. Thanks toMicrosoft to provide excellent explanation of the error :/.
 
Hi,I'm trying to create a pivot table in Excel 2007, something I have done a thousand times in 2003. However, no matter what I do, the wizard just tells me that the data source reference is not valid.I have a table with ITEM NAME, COMPANY NAME, QNT, PRICE, LINE PRICE etc as columns. Each column contains only one data type (ie either a number, or a currency, or a date etc) and the whole table is set as a table with the name SALES.Whether I type the name of the table, or let the wizard select it, or type a range of cells, or drag using the selector in the wizard, i just says the same thing.Even tables from the 2003 sheets which work fine in pivot tables dont work in 2007 !!!Any idea why ?Olly
Click to expand...

Try saving the file under a new file name...then create the pivot table. Milo
 
Maybe it has to do when you are opening the excel file from a website.
It's opening the file from a temporary folder.

When I save it somewhere with the same filename it just works!

Had the same problem.
Click to expand...

It looks like the problem is the source data is still read only. When you save it locally it changes to read/write and creating pivots start to work again.
 
Maybe it has to do when you are opening the excel file from a website. It's opening the file from a temporary folder. When I save it somewhere with the same filename it just works!Had the same problem."Oliver Marshall" wrote:> Weirdest thing. I saved it with a new file name (sales1.xlsx rather > than sales.xlsx) and it worked just fine !!> > How odd. > > > Ta anyway>
Click to expand...

Thanks Bart - I had the same issue.
 
Maybe it has to do when you are opening the excel file from a website.
It's opening the file from a temporary folder.

When I save it somewhere with the same filename it just works!

Had the same problem.
Click to expand...

Great help, i just resolved my same problem ... great help :)
 
Thanks for the tips guys, I was struggling to pivot data and it not read the source file (it transpired that I was trying to pivot webpage data). once saved to an XLS file it worked without any issues. Cheers.
 
I got this error message when I tried to create a pivot table in a file which had the following characters within a filename:

+ ] [ ^ & ~ #

It seems like Excel doesn't like them.
Remove any of these and the error should be gone.
 
I got this error message when I tried to create a pivot table in a file which had the following characters within a filename:



+ ] [ ^ & ~ #



It seems like Excel doesn't like them.

Remove any of these and the error should be gone.
Click to expand...

Thank you for the tip!
 
Hi,I'm trying to create a pivot table in Excel 2007, something I have done a thousand times in 2003. However, no matter what I do, the wizard just tells me that the data source reference is not valid.I have a table with ITEM NAME, COMPANY NAME, QNT, PRICE, LINE PRICE etc as columns. Each column contains only one data type (ie either a number, or a currency, or a date etc) and the whole table is set as a table with the name SALES.Whether I type the name of the table, or let the wizard select it, or type a range of cells, or drag using the selector in the wizard, i just says the same thing.Even tables from the 2003 sheets which work fine in pivot tables dont work in 2007 !!!Any idea why ?Olly
Click to expand...

I have several workbooks set up with pivot tables that reference a range ofcells called "Database". When I converted to Excel 2007 I started getting this error message. If you go to Formulas/Name Manager I found a duplicate named range with #REF where the cell range should be - as well as the correct definition below it. Apparently the pivot tables link to the first reference. If you delete the first one the pivot tables work fine.
 
I just discovered another reason for why the pivot table feature may not work. If you are opening an excel file from a link and don't first save the document, it will say that the pivot table reference is is invalid. Saving the document corrects the problem for me.
 
I did a Save As and that worked.
- original file was a txt based .csv file
- Saved as an .xlsx
 
18 ноември 2013, понеделник, 23:20:47 UTC+2, (e-mail address removed)напиÑа:
I did a Save As and that worked.

- original file was a txt based .csv file

- Saved as an .xlsx
Click to expand...

When opening an Excel workbook that contains square brackets in the name (e..g., "foo[1]"), the user will receive the following error message when attempting to create PivotTables using data from within the workbook. The causeis due to square brackets being invalid characters in an Excel workbook file name.

This issue may be seen when trying to open an Excel workbook from Internet Explorer. Internet Explorer creates a copy of the file in the temporary directory, renaming the file with square brackets. This can be avoided by clicking "Save," instead of "Open," when Internet Explorer presents the file tothe user.
 
Important safety tip: if you're also trying to add a pivot table to a file you have just opened in Outlook email it's probably in read only mode. You'll get the same error "Data Source Reference Not Valid".

You need to save the file locally and then you can create the pivot table. ; )

Top reasons for that error

* Blank Column Names
* Hidden Columns (that have blank column names)
* Column names that lead with blanks (happens with CSV files)
* File not saved locally where it can be edited (temp or Outlook folder), save it locally.

Good luck
 
Hi,

I'm trying to create a pivot table in Excel 2007, something I have
done a thousand times in 2003. However, no matter what I do, the
wizard just tells me that the data source reference is not valid.

I have a table with ITEM NAME, COMPANY NAME, QNT, PRICE, LINE PRICE
etc as columns. Each column contains only one data type (ie either a
number, or a currency, or a date etc) and the whole table is set as a
table with the name SALES.

Whether I type the name of the table, or let the wizard select it, or
type a range of cells, or drag using the selector in the wizard, i
just says the same thing.

Even tables from the 2003 sheets which work fine in pivot tables dont
work in 2007 !!!

Any idea why ?

Olly
Click to expand...

Make sure that you've either Converted it or saved it to the .xlsx file format. Then re-open and it should work. That was my solution.
 
Back
Top