+ Reply to Thread
Results 1 to 4 of 4

Sumproduct formula linking to external workbook works in one file, but not another

Hybrid View

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    Vernon
    MS-Off Ver
    Excel 2010
    Posts
    2

    Sumproduct formula linking to external workbook works in one file, but not another

    Hi all,

    I am having a problem with a SUMPRODUCT formula that I am using to reference an external workbook and am hoping to get a little help.

    The formula is a replacement for a SUMIFS formula (which took long enough to figure out, but we got there), so the user will not have to have the external file open in order for the links to update properly. The problem I am having is that the formula works in one workbook, but not in others. Here is the formula: (SUMPRODUCT(--([Database.xlsx]Converted!$A:$A=C$1),--([Database.xlsx]Converted!$G:$G="d"),--([Database.xlsx]Converted!$E:$E))/1000)

    So this formula works great in certain files, but in others it returns #VALUE and I can't figure out why. The excel versions are all the same so I don't think that would be it. Does anyone have any idea why a sumproduct formula would work in one workbook but not another? It's strange because I am certain the formula is correct. If I am in a file that works and change the value of C$1 to the actual text value from a file that doesn't work, it will work fine! So from what I can tell it's seems to be workbook based and not due to formula or data errors.

    My forehead is getting sore from hitting the desk.

    Help is greatly appreciated!
    Last edited by needmorejam; 05-07-2015 at 04:49 PM.

  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
    53,047

    Re: Sumproduct formula linking to external workbook works in one file, but not another

    Hi, welcome to the forum

    1st, I would suggest that you avoid full-column ranges in SP, they can make your file begin to slow down
    2nd are you sure that the file names are correct? (no extra spaces anywhere?)
    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
    10-17-2012
    Location
    Vernon
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Sumproduct formula linking to external workbook works in one file, but not another

    Hi FDibbins,

    I adjusted the ranges so the are not full columns and now it works! I had known that you shouldn't use full columns with SP, but it worked initially with my first try so I didn't think about it. No idea why it would work in some files and not others, but who cares I can finally move on.

    Thanks for your help!
    Last edited by needmorejam; 05-07-2015 at 04:52 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
    53,047

    Re: Sumproduct formula linking to external workbook works in one file, but not another

    Glad it worked out for you

+ 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. Replies: 2
    Last Post: 05-31-2013, 08:45 AM
  2. Replies: 2
    Last Post: 03-07-2013, 05:18 AM
  3. [SOLVED] Why this formula only works when the external link file is open
    By zjianguk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2013, 06:18 AM
  4. [SOLVED] Linking to external workbook
    By Steve in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2005, 11:05 PM
  5. Linking to an external workbook
    By haz115 in forum Excel General
    Replies: 1
    Last Post: 02-25-2005, 06:12 PM

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