+ Reply to Thread
Results 1 to 4 of 4

DSUM Link to Other File Causing Value Error

  1. #1
    Registered User
    Join Date
    04-13-2011
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    14

    DSUM Link to Other File Causing Value Error

    Hello. My assistant and I are creating some financial documents in Excel 2010. We set up a DSUM formula that links to a trial balance Excel file in another worksheet. At times, when we open the file using the DSUM formula, if the trail balance is not open, all the DSUM formulas become VALUE errors until we open up the trial balance file.

    Is there a way to make it so these VALUE errors will not show up without having to always open the trial balance file? We tried to use the edit links and update the links, but that didn't work. I was hoping there is a way around this problem. Thanks for anyone that can help us!

    Dave

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: DSUM Link to Other File Causing Value Error

    You could use the Data-Import feature to import the data into a seperate table within the active workbook. Then you have your DSum formula go to the imported data rather than the trial balance workbook.

    Or, if you simply want the cells to be blank if the trial balance worksheet is not open, then you would use something like:

    =IFERROR(DSUM(),"")

  3. #3
    Registered User
    Join Date
    04-13-2011
    Location
    Fort Wayne, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: DSUM Link to Other File Causing Value Error

    So what you're telling me is that unless I do what you're talking about above, there's no way to work around this issue if I don't have the trial balance workbook open?
    Last edited by tleaton; 04-14-2011 at 12:09 PM.

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: DSUM Link to Other File Causing Value Error

    It looks like a possibel workaround is using an array formula. DSUM itself does not work with closed workbooks, but you can recreate the same effect using alonger array formula.

    http://support.microsoft.com/kb/260415

    http://www.ozgrid.com/Excel/sum-if.htm

    Something like

    =SUM(IF([Source]Sheet1!A2:A25="Bourbon",IF([Source]Sheet1!C2:C25>VALUE("7-Apr-2005"),IF([Source]Sheet1!E2:E25="Low",[Source]Sheet1!B2:B25)))

    Confirm with CTRL+SHIFT+ENTER rather than just ENTER.

+ 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