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,FALSE))
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.
|