+ Reply to Thread
Results 1 to 2 of 2

References to external XLS files

  1. #1
    Registered User
    Join Date
    01-17-2006
    Posts
    5

    References to external XLS files

    This has never been a problem for me, but have recently changed jobs, and now I'm having a problem.

    I have 2 sales reps, each with their own 'status spreadsheet' (FileA & FileB); these files remain open most of the day. I also have a 3rd sheet (Summary) which uses different formulas (COUNTIF, VLOOKUP, etc) that reference FileA & FileB to provide instant feedback on their progress (of couse, only as of the last save).

    When I open my summary sheet, all cells referencing the other 2 spreadsheets show as "#VALUE!". The values only populate if I open the other 2 spreadsheets, and then everything's fine. I want to open only my summary sheet, so I don't lock out my reps from updating theirs while I review metrics.

    All 3 spreadsheets live in the same subdirectory, and I have full rights to that subdirectory.

    The 'Calculation' tab of the options dialog has "Update remote references", "Save external link values", and "Accept labels in formlulas" all checked. Calculation is set to update automatically.

    Updating the 'Links' does no good, links box shows as "OK".

    I'm stuck.
    Last edited by goto_guy; 01-17-2006 at 12:36 PM.

  2. #2
    Dave Peterson
    Guest

    Re: References to external XLS files

    There are some functions that don't work with closed workbooks--=sumif(),
    =countif(), =indirect() are a few. But =vlookup() should not cause any trouble.

    And there are replacements (array formulas =sum(if()) or =sumproduct()) that can
    be used in place of the first two.



    goto_guy wrote:
    >
    > This has never been a problem for me, but have recently changed jobs,
    > and now I'm having a problem.
    >
    > I have 2 sales reps, each with their own 'status spreadsheet' (FileA &
    > FileB); these files remain open most of the day. I also have a 3rd
    > sheet (Summary) which uses different formulas (COUNTIF, VLOOKUP, etc)
    > that reference FileA & FileB to provide instant feedback on their
    > progress (of couse, only as of the last save).
    >
    > When I open my summary sheet, all cells referencing the other 2
    > spreadsheets show as "#N/A". The values only populate if I open the
    > other 2 spreadsheets, and then everything's fine. I want to open only
    > my summary sheet, so I don't lock out my reps from updating theirs
    > while I review metrics.
    >
    > All 3 spreadsheets live in the same subdirectory, and I have full
    > rights to that subdirectory.
    >
    > Updating the 'Links' does no good, links box shows as "OK".
    >
    > I'm stuck.
    >
    > --
    > goto_guy
    > ------------------------------------------------------------------------
    > goto_guy's Profile: http://www.excelforum.com/member.php...o&userid=30557
    > View this thread: http://www.excelforum.com/showthread...hreadid=502083


    --

    Dave Peterson

+ 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