References to external XLS files

[goto_guy]

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.

 

[Dave Peterson]

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.