Solved: Pivot "Reference not Valid"

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
Saved a new Excel 2010 spreadsheet, but uses the same pivot table. When refreshing I get "reference not Valid" error.

I found something in a search. http://www.excelforum.com/tips-and-tutorials/715531-pivot-reference-not-valid-solution.html
where they say to delete the table range, and add it again to solve this.

But I noticed that the Name Manager does not permit one to delete Table Range.

Also in the source file, with uses data from an ODBC table, columns with formulas are not pulling the data as it should.

Any ideas?

Edit: when I open the new file, it says there's a link to old file, I try to update but it cannot.
 

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
Update: I broke any links so not getting the error message, but the formulas in the source tables is not extracting any dara or doing the calculations.
 

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
Ok, I am pulling data in Excel Table, and have set up formulas in adjoining columns to extract information.

But that information is not pulling any data according to formula.

A web search I obtained this youtube instruction when your formulas aren't working, its a format issue.

ie: numbers aren't added because the numbers are stored as text, etc.

A fix presented was ALT + D E F

a shortcut for Data, tExt, to columns, Finish


this did not fix anything.

I'm certain its a formatting issue, but how to resolve.
 

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
OK, some progress.

my formula in col M is designed to pull number or result from col D which contains a formula,

Col M returns nothing or zero.

Col D's formula actually returns the number 2012.


Now, if I manually input in Col D the number 2012, then the formula in Col M works.


The strange part as other spreadsheets I have it all works.
 

holymoly

Thread Starter
Joined
Jun 21, 2003
Messages
635
:I've done this before, but one way around is to insert a column with formula =Value(a1) to return a number value.

Solved, sort of.

thanks for your help and listening :cool:
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top