Data source reference is not valid in pivot table wizard

[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

[Roger Govier]

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

--
Regards
Roger Govier

"Oliver Marshall" <oliver....@gmail.com> wrote in message
news:a0b4c95b-5cb7-4506...@y5g2000hsf.googlegroups.com...

[Oliver Marshall]

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

[Bart]

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.

[chelse...@gmail.com]

I had the same problem - until I saved with a new name, it wouldn't work. Thanks for the tip!

[mch...@gmail.com]

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 to Microsoft to provide excellent explanation of the error :/.

[nwi...@greenvillemetals.com]

On Saturday, November 17, 2007 12:27:09 PM UTC-5, Oliver Marshall wrote:
> 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

Try saving the file under a new file name...then create the pivot table. Milo

[jim.wink...@gmail.com]

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.

[Dan]

On Wednesday, December 19, 2007 5:01:01 AM UTC-8, Bart wrote:
> 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>

Thanks Bart - I had the same issue.

[raml...@gmail.com]

Great help, i just resolved my same problem ... great help :)

[beaumo...@gmail.com]

On Tuesday, April 2, 2013 2:11:58 AM UTC+1, raml...@gmail.com wrote:
> On Wednesday, December 19, 2007 9:01:01 PM UTC+8, Bart wrote:
>
> > 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.
>
> >

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.

[pms...@gmail.com]

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.

[new...@gmail.com]

Thank you for the tip!

[dboydj...@gmail.com]

On Saturday, November 17, 2007 12:27:09 PM UTC-5, Oliver Marshall wrote:

> 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

I have several workbooks set up with pivot tables that reference a range of cells 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.

[metalsup...@gmail.com]

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.

[cody....@gmail.com]

I did a Save As and that worked.
- original file was a txt based .csv file
- Saved as an .xlsx

[gmus...@gmail.com]

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 cause is 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 to the user.

[richard...@gmail.com]

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

[jim.j...@gmail.com]

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.

[thanks]

Thanks Oliver, weird but helped me too!