@vlookup bug?

Can someone help with this,

1.    Create a spreadsheet in a file (test1) with a small table in it and 
give that table a name. Use labels for the first column and numbers in the 
next column.

2.    Create a spreadsheet in another file (test 2) and use the @vlookup 
command to reference the table you created in the "test1" file.  All
seems 
well.

3.    Close all files.  Now open the "test1" file.  Then open the
"test2" 
file. Do not close the "test1" file before opening "test2". 
They must both 
be in memory.   Err is reported in all the formulas which reference the open 
linked (test1) file.

If you close all files again and simply open the "test2" file, the
"err's" 
go away and the formula computes correctly.

Essentially, if the dependent file of a file conatining the @vlookup command 
is opened first, the formula errors.  Both files cannot be opened at the 
same time unless you open the file containing the @vlookup formula first.

Any help would be appreciated.

Karol 

I could not duplicate your problem on xp with 13.0.0.565.

@VLOOKUP("B",[Test1]A:A1..B4,1,1)

Karol wrote:
> Can someone help with this,
> 
> 1.    Create a spreadsheet in a file (test1) with a small table in it and 
> give that table a name. Use labels for the first column and numbers in the

> next column.
> 
> 2.    Create a spreadsheet in another file (test 2) and use the @vlookup 
> command to reference the table you created in the "test1" file. 
All seems 
> well.
> 
> 3.    Close all files.  Now open the "test1" file.  Then open the
"test2" 
> file. Do not close the "test1" file before opening
"test2".  They must both 
> be in memory.   Err is reported in all the formulas which reference the
open 
> linked (test1) file.
> 
> If you close all files again and simply open the "test2" file,
the "err's" 
> go away and the formula computes correctly.
> 
> Essentially, if the dependent file of a file conatining the @vlookup
command 
> is opened first, the formula errors.  Both files cannot be opened at the 
> same time unless you open the file containing the @vlookup formula first.
> 
> Any help would be appreciated.

Like Ken, I didn't encounter your type of problem in performing this test (using

QP8).

HOWEVER [comma] I did experience similar problems in the past with external file

references in @VLOOKUP!  Things worked fine initially for me, but after six 
months or so one file after another developed problems returning the proper 
lookup values and either returned garbage or ERR.  Erasing the entire formula, 
saving the file, and recreating the formula from scratch seemed to get rid of 
the problem ... for a while, until things went haywire again, for reasons I 
never fathomed despite strenuous investigation of possible causes.  Using 
external file references (including websites) in @VLOOKUP (and other functions,

by the way) seems to carry the potential for some type of memory conflict to 
establish itself at some point along the way and then persist with uncanny
tenacity.

To make a long story short, I've gotten away from using this type of real-time 
cross-file data use in conjunction with functions ... and all my spreadsheets 
have returned to normal behavior.  The simple alternative is to IMPORT the 
relevant data table into the target file via macro procedure, to some designated

parking space.  The target file then simply applies a @VLOOKUP to that parking 
space and thus gets what it needs from inside its own file -- simple, clean, 
fast, and easy to manage.  The only exception to this are simple file references

(e.g., +[myfile]stuff:A1), which I still use sparsely, but those references are

not used as parameters in a function.

Cheers,
Uli

Thanks for responding,

To duplicate my scenario, I must be clearer.  Your 
@VLOOKUP("B",[Test1]A:A1..B4,1,1) does not reference a name after
[Test1] in 
the formula.  Create a named range called "Data" for A:A1..B4 in Test1
and 
use the "Data" name in your formula rather than cell references as in

@VLOOKUP(B4,[Test1]Data,1,1).  Open the Test1 file then the file containing 
your @vlookup formula and you will find ERR in the reference to the Test1 
file.

If you use cell references in the @vlookup formula to refer to an external 
spreadsheet rather than a Named Range, it works correctly?  This is just 
terribly inconvenient when all of my data is set up in named ranges.

Please let me know what you come up with.

Thanks,
Karol



"Kenneth Hobson" <khobson@aaahawk.com> wrote in message 
news:478ead71_3@cnews...
>
> I could not duplicate your problem on xp with 13.0.0.565.
>
> @VLOOKUP("B",[Test1]A:A1..B4,1,1)
> 

Using named ranges in lookup functions have caused me similar grief in 
other applications like Excel in the past.  This is why I don't use 
them.  I would have to test it to see if that happens in Excel now.

Using a name range for your method, @VLOOKUP("B",[Test1]MyRange,1,1),

duplicates your problem.  Opening just Test2.qpw, Update References 
option works fine.

You can use a macro to check for ERR message if needed and take actions 
like closing the Reference notebooks or use a macro as Uli detailed to 
update cell values.