Hello Excel Mavens,
I have worksheet linked to another workbook.
I have an equation that pulls data from that linked workbook.
When I open the workbook, the data does not get updated and I get the #VALUE
error. If I open the linked workbook the data updates.
Other linked equations (to the same workbook) refresh perfectly.

The equation is:=COUNTIF('S:\[Nov 05 Recap.xls]Sheet1'!$E$7:$G$21,O4)
This is Excel 2003 Pro (both workbooks)

Any ideas would be appreciated.

Brian

Excel has difficulty executing some formulas with links to closed
workbooks. I can't say I am surprised since it must have to do a lot
of work to determine the contents of the cells without fully opening
the workbook concerned.

Ways in which you might be able to help it:
- open the file<g (you could do that by macro on opening the
destination file, and you could open it read-only to reduce the
interaction with other users)
- include the formula result in a cell in the source workbook and just
reference that single cell in the linked formula
- use a named range in the source workbook rather than an address like
$E$7:$G$21 (I'm not sure this helps, but it might; it certainly makes
maintenance simpler because you can insert rows and columns in the
source workbook without having to remember to have the destination book
open at the time).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks Bill,
Those are the exactly the things i thought i would try.
I was hoping that I had missed something in the link settings (smile).
I hate to use named ranges since my users are Excel nubs and barely
understand the concept of linking.

All the best from rainy Olympia, Washington

Brian

"Bill Manville" wrote:

Excel has difficulty executing some formulas with links to closed
workbooks. I can't say I am surprised since it must have to do a lot
of work to determine the contents of the cells without fully opening
the workbook concerned.

Ways in which you might be able to help it:
- open the file<g (you could do that by macro on opening the
destination file, and you could open it read-only to reduce the
interaction with other users)
- include the formula result in a cell in the source workbook and just
reference that single cell in the linked formula
- use a named range in the source workbook rather than an address like
$E$7:$G$21 (I'm not sure this helps, but it might; it certainly makes
maintenance simpler because you can insert rows and columns in the
source workbook without having to remember to have the destination book
open at the time).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup