+ Reply to Thread
Results 1 to 11 of 11

Sumproduct formula returns #ref when linked data file is closed.

  1. #1
    Registered User
    Join Date
    06-23-2014
    Location
    Napa
    MS-Off Ver
    2010
    Posts
    4

    Sumproduct formula returns #ref when linked data file is closed.

    Hello
    Sumproduct is now my favorite new tool in excel - however, I'm having trouble with the formula when it includes a reference to a table in a closed excel file. The formula works fine when the file is open, but as soon as I close the file and then click anywhere in my results file (or hit save) the formula results in #ref error. The other file has multiple tabs and is quite large. If I copy one of the tables to a new file and reference my formula to the new file it will work fine.
    Any suggestions would be welcome!
    Shandra

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sumproduct formula returns #ref when linked data file is closed.

    hi and welcome to the forum

    what is the formula? I suspect you have INDIRECT() in there somewhere? INDIRECT() only works on open WB's
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-23-2014
    Location
    Napa
    MS-Off Ver
    2010
    Posts
    4

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Here is my formula-
    =SUMPRODUCT(('H:\Business Plans\2014\2014VWCSIP_Conservative.xlsx'!Wholesale[SalesYear]=C30)*('H:\Business Plans\2014\2014VWCSIP_Conservative.xlsx'!Wholesale[JanuaryCs]))
    The table name is Wholesale. Odd thing is when I copied the one of the tables to a blank workbook the formula holds. Weird - the problem seems to be with the file...?


    And thank you! I've definitely benefited from surfing this site for quite some time!
    Last edited by shandrak; 06-23-2014 at 07:45 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Is that summing or counting?

    Try and replace it with countif() or sumif()

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumproduct formula returns #ref when linked data file is closed.

    From this site:

    http://office.microsoft.com/en-us/ex...010155686.aspx

    If a workbook contains an external link to an Excel table in another workbook, that linked "source" workbook must be open in Excel to avoid #REF! error codes in the "destination" workbook that contains the links. If you open the destination workbook first and #REF! error codes appear, they will be resolved if you then open the source workbook. If you open the source workbook first, you should see no error codes
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Quote Originally Posted by FDibbins View Post
    Is that summing or counting?

    Try and replace it with countif() or sumif()
    It's summing.

    Neither of those will work on a closed file.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Thanks for the input Tony, I tried google, but my search was inconclusive

  8. #8
    Registered User
    Join Date
    06-23-2014
    Location
    Napa
    MS-Off Ver
    2010
    Posts
    4

    Re: Sumproduct formula returns #ref when linked data file is closed.

    It is summing. Sumif() doesn't work on either my real data file or my test one (whereas sumproduct() works in test).

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumproduct formula returns #ref when linked data file is closed.

    The solution would be to use A1 references instead of the structured table references.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Quote Originally Posted by FDibbins View Post
    Thanks for the input Tony, I tried google, but my search was inconclusive
    That's one of the tidbits I have in my Notes folder.

    http://www.excelforum.com/the-water-...-formulas.html

  11. #11
    Registered User
    Join Date
    06-23-2014
    Location
    Napa
    MS-Off Ver
    2010
    Posts
    4

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Thanks Tony! I was hoping to use structured references since the data file will on occasion be edited (including insertion of more rows). Frustrating because the test version I created does keep the value in the result cell (at least until I click in the cell) whereas the link to my real database defaults to #ref error immediately upon clicking anywhere, so I figured it must be something with my database file rather than the formula itself.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumproduct formula returns #ref when linked data file is closed.

    Let's assume the sheet name is WholeSale and SalesYear is in column A, A2:An and JanuaryCs is in column B, B2:Bn.

    Then your formula would be:

    =SUMPRODUCT(--('H:\Business Plans\2014\[2014VWCSIP_Conservative.xlsx]Wholesale'!A2:A500=C30),'H:\Business Plans\2014\[2014VWCSIP_Conservative.xlsx]Wholesale'!B2:A500)

    Use a big enough range that allows for additional data in the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to update linked data from a closed source
    By Charlie2106 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 09:17 AM
  2. Retrieve formula linked to closed xls file
    By tek9step in forum Excel General
    Replies: 4
    Last Post: 12-11-2009, 08:25 AM
  3. Excel 2003 Linked data returns Cell Reference Display
    By JBG2007 in forum Excel General
    Replies: 3
    Last Post: 10-10-2006, 01:36 AM
  4. FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
    By Tomkat743 in forum Excel General
    Replies: 5
    Last Post: 04-07-2006, 09:35 AM
  5. FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED
    By Tomkat743 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2006, 09:35 AM

Tags for this Thread

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