Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel file links now display as #VALUE! (excel97)

    hiya

    we have a main excel file that contains many links to other excel files.
    when we open up the main excel file the column with the links now display as #VALUE! - however in the edit bar displays the correct file link details. ie =SUMIF('COCSOPENDATAMELCCWATOMASZE[136V501_.XLS]Calc (3)'!$AC$3:$BH$831,A839,'COCSOPENDATAMELCCWATOMASZE[136V501_.XLS]Calc (3)'!$BH$3:$BH$831)

    when the main excel file is opened> then we open up the linked file - in the same session of excel - the main excel file then displays the correct linked values.
    when we close the linked file - the column the again displays #VALUE!.

    previously when we opened the main excel file it would display the correct data. displaying #VALUE! in the column - this has just started occuring recently .
    any ideas?
    Diana

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: excel file links now display as #VALUE! (excel97)

    Hi Diana,

    The change in behaviour might be because someone's unchecked the 'Ask to update automatic links' option under Tools|Options|Edit.

    The #VALUE display is what you get any time links to a closed workbook in a SUMIF function is updated. If you open the target workbook and click 'No' when prompted to update the links, the last calculated value will be displayed. However, if you update the links, you'll get #VALUE until the source workbook is opened.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel file links now display as #VALUE! (excel97)

    hiya

    when opening up the excel file the message appears - regardless if we click yes update> or no to update - teh #VALUE! is still being displayed?
    any ideas?
    Diana

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: excel file links now display as #VALUE! (excel97)

    Hi Diana,

    If the file is saved with #VALUE! displayed, then this is what you'll get next time the file is opened. To correct it, open both files so that the links refresh, then close the source file and save the target file.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: excel file links now display as #VALUE! (excel97)

    Do you have any "indirect links" (links built by using the indirect function)?

    These will not work unless the linked file is open.

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel file links now display as #VALUE! (excel97)

    Functions such as SUMIF and COUNTIF can only work with references to an open workbook. Try SUMPRODUCT instead:

    =SUMPRODUCT(N('COCSOPENDATAMELCCWATOMASZE[136V501_.XLS]Calc (3)'!$AC$3:$A$831=A839),'COCSOPENDATAMELCCWATOMASZE[136V501_.XLS]Calc (3)'!$BH$3:$BH$831)

    The N function coerces the result of the comparison (TRUE/FALSE) into a number, with TRUE = 1 and FALSE = 0. Note: the comparison should use only one column; so I changed $AC$3:$BH$831 to $AC$3:$A$831.

Posting Permissions

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