Plugging data from non-open Workbooks

[Herbert Chan]

Hello,

I have a number of databases stored on Excel Workbooks and I want to create
another workbook to get information from those databases. I will make
reference to a number of the databases with the following:

COUNTIF("worksheet name followed by filename", "=blablabla")

but I don't want to open them as they are like each 5Mb in size and I have
to refer like 10 of them on a worksheet.

I've tried opening one of the database and then reference it by
point-and-clicking and then closing it. The formula works fine when the file
is open. When it's closed and I try updating the worksheet by pressing F9,
the cell changes to #VALUE. Why's that?

Anyone can help me?

Herbert

 

[Herbert Chan]

Just a quick extra info: I'm using excel 97.

Herbert

 

[Ken Wright]

You need to insert the full filepath with the reference if the workbook is closed, eg:-

='C:\4 Temp\Test\[Test5.xls]Sheet1'!$B$3

 

[Herbert Chan]

I've already done that but it still gives #VALUE. I've searched around the
net and it is said that this is a bug and it's only fixed in 2002.

Is that true?

Herbert

You need to insert the full filepath with the reference if the workbook is closed, eg:-

='C:\4 Temp\Test\[Test5.xls]Sheet1'!$B$3

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --

Just a quick extra info: I'm using excel 97.

Herbert

the
file

 

[Dave Peterson]

And see one more workaround suggestion at your other post.