1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Pivot "Reference not Valid"

Discussion in 'Business Applications' started by holymoly, Jan 22, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. holymoly

    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.
     
  2. holymoly

    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.
     
  3. holymoly

    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.
     
  4. holymoly

    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.
     
  5. holymoly

    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:
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086436

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice