Hi
the problem is that the source file is not in the same directory on
your different PCs (or lets say they have different drive letters
assigned)

--
Regards
Frank Kabel
Frankfurt, Germany

"Dread_Pirate_Roberts"
schrieb im Newsbeitrag
...
Before opening other file:

=CONCATENATE(VLOOKUP(L$2,'G:\Office\[Joblist.xls]Sheet1'!$A$2:'G:\Offic
e\[Joblist.xls]Sheet1'!$B$301,2,FALSE),"

",VLOOKUP(L$2,'G:\Office\[Joblist.xls]Sheet1'!$A$2:'G:\Office\[Joblist.
xls]Sheet1'!$C$301,3,FALSE))

After opening other file on same machine:

=CONCATENATE(VLOOKUP(L$2,[Joblist.xls]Sheet1!$A$2:[Joblist.xls]Sheet1!$
B$301,2,FALSE),"

",VLOOKUP(L$2,[Joblist.xls]Sheet1!$A$2:[Joblist.xls]Sheet1!$C$301,3,FAL
SE))

After opening other file on other machine:

=CONCATENATE(VLOOKUP(L$2,'T:\MYDocuments\[Joblist.xls]Sheet1'!$A$2:'T:\
MYDocuments\[Joblist.xls]Sheet1'!$B$301,2,FALSE),"

",VLOOKUP(L$2,'T:\MYDocuments\[Joblist.xls]Sheet1'!$A$2:'T:\MYDocuments
\[Joblist.xls]Sheet1'!$C$301,3,FALSE))

Hopefully this explains it.

I would have expected the after openings to be the same. Could this
maybe
have something to do with LINKS?
"Dread_Pirate_Roberts" wrote:

Ok more info...
I went and checked, and yes the full path was there before the
external
reference was opened, and changed to without full path when the
other sheet
was opened. However, the path seems to be changing depending upon
the
computer used, ie, it is defaulting to a different directory rather
than the
one I specified in the function.

Is this a result of the startup directory or is there another
qualifier I
need to put into the function to keep it from changing?

"Dread_Pirate_Roberts" wrote:

Humm,
well here is the complete formula...

=CONCATENATE(VLOOKUP(D$2,[Joblist.xls]Sheet1!$A$2:[Joblist.xls]Sheet1!$
B$301,2,FALSE),"

",VLOOKUP(D$2,[Joblist.xls]Sheet1!$A$2:[Joblist.xls]Sheet1!$C$301,3,FAL
SE))

it works fine on 4 machines. They all have a copy of the
spreadsheet, and
have access to the shared "Joblist" spreadsheet. On 1 machine,
Excel replaces
[Joblist.xls]Sheet1 with 'T:\office\[Joblist.xls]Sheet1' for
all
occurances. I have a macro setup that opens the Joblist
spreadsheet. This
is done via a button. I'd really like the Joblist to be opened
automatically
when this sheet is opened, but for now the button trick works for
all the
machines except 1.

If I explicitly typed in the path would that solve the problem?

"Frank Kabel" wrote:

Hi
this does not look like a valid formula. I'd guess it should
read:
VLOOKUP(D$2,'[Joblist.xls]Sheet1'!$A$2:$B$301,2,FALSE)

The file name is repalced with the complete path if the other
workbook
is NOT opened. No way around this except opening the other file
:-)


--
Regards
Frank Kabel
Frankfurt, Germany

"Dread_Pirate_Roberts"

schrieb im Newsbeitrag
...
I am using a function to do a lookup on external shared
spreadsheet.
The
formula goes like this:


VLOOKUP(D$2,[Joblist.xls]Sheet1!$A$2:[Joblist.xls]Sheet1!$B$301,2,FALSE
)
This works great. However, on another machine, Excel replaces
[Joblist.xls]Sheet1 with 'T:\office\[Joblist.xls]Sheet1'.
Why is this being replaced, and how do I get rid of the
compete path?
Is
there an option setting that is causing this?

Thanks in advance.