msExcel Link #value! 1

[hughLg]

I've a workbook that link to other workbook in a formula. When i open the destination
workbook without opening the source workbook the result of the formula is #value!.

It looks correct if both the source and destination files are opened at the same time.

 

[lagerplease]

I think you've answered your own question in your own post. If the source workbook isn't open then the destination workbook obviously won't be able to find the data it requires to fulfill the formula. By necessity, you need to have both workbooks open for it to function properly.

Think of it this way ... to find out how much money you have in your pocket you need to access your pocket. If you take your trousers off, all access to that pocket is gone and you can't find out what's in it!

 

[Lilliabeth]

If the source workbook isn't open then the destination workbook obviously won't be able to find the data it requires to fulfill the formula. By necessity, you need to have both workbooks open for it to function properly.

Think of it this way ... to find out how much money you have in your pocket you need to access your pocket. If you take your trousers off, all access to that pocket is gone and you can't find out what's in it!

That's not how linking formulas behave at all. Please create a linking formula, close the source workbooks, and see for yourself.


hughLg, Whenever a formula gives you an error or questionable answer, the first thing to do is read the formula. maybe it would help for you to post the formula that is resulting in the error.

Are the source workbooks located on your local machine?

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Gavona]

If you create a linking formula to a "source" workbook that is in memory (open) at the time and then save and close the "destination" file with the link then I suspect it is not 'remembering' the path to the linked file.

So, open both files. Close the source file first. Then save the other [destination] file.
Problem solved?


Gavin

 

[macropod]

Hi Hugh,

If the source workbook was created by another App (especially Access), the link won't work correctly until that workbook has been opened and saved - but this usually results in a "Unable to Read File" error. See:

http://support.microsoft.com/kb/824172/en-us

External links might also fail when you open a file that was last saved in a previous version of Excel. See

http://support.microsoft.com/kb/327006/en-us

Some Excel formulae (eg INDIRECT) also only work when the source workbook is open - but this usually results in a #REF! error. See:

http://support.microsoft.com/kb/151323/en-us

For Excel 2002, there was also a problem with links to workbooks with more than 1024 characters in the link path, but this was fixed way back in November 2002.

Cheers

[MS MVP - Word]