+ Reply to Thread
Results 1 to 4 of 4

Formula linking to external data import returning #REF

  1. #1
    Forum Contributor
    Join Date
    02-25-2008
    Location
    Windsor, ON Canada
    MS-Off Ver
    Excel 2007, 2003
    Posts
    119

    Formula linking to external data import returning #REF

    I'm pulling data into Excel from an external link to an Access database. This information is being pulled in in segments, and they will need to be compiled on one worksheet and subsequently imported into SAP (it's building a journal entry).

    Currently I have a separate worksheet with formulas pointing to the data on the other tabs which pull data in from Access via a connection. The problem is that when I refresh the data, all my formulas return #REF errors.

    Is there a way to avoid this?
    Last edited by 2709236; 11-08-2011 at 10:16 AM.

  2. #2
    Forum Contributor
    Join Date
    02-25-2008
    Location
    Windsor, ON Canada
    MS-Off Ver
    Excel 2007, 2003
    Posts
    119

    Re: Formula linking to external data import returning #REF

    Bumping this question back to the current stream. . .

    Maybe I was too vague or this is just a dumb question. Either way, I'll try to rephrase and hopefully someone will respond.

    The issue is that I'm trying to reference a cell that is linked to a table being pulled in from Access, but when it refreshes my formulas return #REFs. I think I may have my answer now, but I'm just looking for clarification.

    If I build my formula as '=' and then click on the cell I want to reference, it gives me a formula like:
    =Table_Query_from_MS_Access_Database etc, etc.

    I'm wondering if I need to manually build the formula in the same way as if I were pointing to a pivot table. Manually reference the worksheet and cell. I will try that today unless someone can confirm that this will not solve my issue.

    Thanks in advance.

  3. #3
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Formula linking to external data import returning #REF

    If you right click on the range where the data is imported, you should see a "Data Range Properties" option. Choose it and then, near the bottom, choose "Overwrite existing cells with new data, clear unused cells".
    Attached Images Attached Images
    Hope that helps,

    Colin

    RAD Excel Blog

  4. #4
    Forum Contributor
    Join Date
    02-25-2008
    Location
    Windsor, ON Canada
    MS-Off Ver
    Excel 2007, 2003
    Posts
    119

    Re: Formula linking to external data import returning #REF

    Thank you very much! I figured I was missing something.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1