Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

  1. #1
    Board Regular
    Join Date
    Aug 2013
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

     
    Excel 2016 - I have an Excel file that imports a table from SQL Server via a connection created with the Data->Get Data->From Database->From Sql Server Database.

    When I built the file, I imported the latest information from the SQL table, which created a table automatically in Excel, and then went to work creating all the formulas I needed to get the summary information I needed.

    All worked well - then I did a second import from the same connection with a new date range, a smaller one, so fewer rows were imported.

    The issue is that, upon import, it appears that rather than clearing the data from the first import and then creating the new table, it actually deletes the rows that are no longer being used. This in turn causes a slew of REF errors and re-shuffling of ranges within all my formulas.

    Does anyone know a way to work around this? I'll give two examples:

    1. In all of my formulas, I reference the pertinent table range as A2:A750000. 750,000 rows is the max rows we ever expect. I don't use the range A:A for two reasons: I don't want A1 included in the range, and doing so greatly impacts performance. So when the new data is imported, that A2:A750000 range gets jacked up in all the formulas that contain it.

    2. The more critical example - I have an IF formula that looks at each cell in column A in the table and returns a number depending on the condition. The aforementioned new import of data throws REF errors for all rows that were populated before, and then are no longer populated with the new refresh. Again, because it appears the new import deletes rows rather than clearing data.

    Any help with this would be greatly appreciated.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,754
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

    Are the External data properties for the resulting Table set to overwrite, or are they still at the default Insert/Delete setting?

  3. #3
    Board Regular
    Join Date
    Aug 2013
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

    Quote Originally Posted by RoryA View Post
    Are the External data properties for the resulting Table set to overwrite, or are they still at the default Insert/Delete setting?
    Hi Rory, this is a very good question. Where would I find this information? I've right-clicked on the connection and clicked Edit, which brings me to a window called Query Editor with many options.

  4. #4
    Board Regular
    Join Date
    Aug 2013
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

    Ah I've found it - a window called External Data Properties.

    It's set to "Insert Cells for new data, delete unused cells"

    It would appear that I need "Overwrite existing cells with new data, clear unused cells"

    I think this should be my answer!!

  5. #5
    Board Regular
    Join Date
    Aug 2013
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

    Thanks RoryA! That's what I needed!

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,754
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel connected to SQL Server - every new import causes REF! errors and issues with formulas

      
    Glad to help.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com