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.

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.

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.

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.

Before opening other file:
=CONCATENATE(VLOOKUP(L$2,'G:\Office\[Joblist.xls]Sheet1'!$A$2:'G:\Office\[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,FALSE))

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

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.

nope.
All the drive mappings for the "Joblist" file is the same. The only
difference is that the originating file is in a different physical directory,
however, the mapping to get there (ie the T:\ mapping) is the same. To
clarify, everyone has a T:\ that points to their own copy of the spreadsheet,
but the G:\mapping points to the same file.

So in a nutshell, everyone has an individual Timesheet that references the
shared Joblist.

That being said, it doesnt make sense why excel would overwrite the correct
direct path with another incorrect one.

"Frank Kabel" wrote:

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.

Frank Kabel wrote...
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)
...

Wrong. As long as this file is open, the colon is syntactically an
operator with the arguments

'[Joblist.xls]Sheet1'!$A$2

and

'[Joblist.xls]Sheet1'!$B$301

which are range references on the same worksheet in the same workbook,
so

'[Joblist.xls]Sheet1'!$A$2:'[Joblist.xls]Sheet1'!$B$301

is the 'runtime' constructed smallest single area range containing
both ranges, which happens to be identical to

'[Joblist.xls]Sheet1'!$A$2:$B$301

On the other hand, when the file is closed, the former expression
becomes

'T:\...\[Joblist.xls]Sheet1'!$A$2:'T:\...\[Joblist.xls]Sheet1'!$B$301

However, when the file is closed, these *AREN'T* range objects any
more, so the expression becomes a syntax error.

So you're right about using the latter syntax, but for the wrong
reason. Only the form

'[Joblist.xls]Sheet1'!$A$2:$B$301

when the file's open and

'T:\...\[Joblist.xls]Sheet1'!$A$2:$B$301

when the file's closed should work in both cases. So the OP should
make this change whether or not s/he understands why it's necessary
(or s/he can live with the error - always the OP's choice whether or
not to ignore advice they can't understand).
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!

Hi Harlan
[...]
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)

Wrong. As long as this file is open, the colon is syntactically an
operator with the arguments
'[Joblist.xls]Sheet1'!$A$2
and
'[Joblist.xls]Sheet1'!$B$301
which are range references on the same worksheet in the same
workbook,
so
'[Joblist.xls]Sheet1'!$A$2:'[Joblist.xls]Sheet1'!$B$301
is the 'runtime' constructed smallest single area range containing
both ranges, which happens to be identical to
'[Joblist.xls]Sheet1'!$A$2:$B$301

thanks for this clarification. Should have tested it before guessing
:-)

Frank

Well thanks for all the input so far, but I am still having the problem of
the drive mapping that is in the expression changing on another pc even tho
all the mappings are the same.

"Dread_Pirate_Roberts" wrote...
Well thanks for all the input so far, but I am still having the problem of
the drive mapping that is in the expression changing on another pc even tho
all the mappings are the same.

How exactly are you distributing these files to your other users?

The Joblist is stationary.
The Timesheet is copied from a master on the server to a personal directory
on the same server.

On 4 machines this worked just fine. On 2 others it doesnt work.

"Harlan Grove" wrote:

"Dread_Pirate_Roberts" wrote...
Well thanks for all the input so far, but I am still having the problem of
the drive mapping that is in the expression changing on another pc even tho
all the mappings are the same.

How exactly are you distributing these files to your other users?

Dread_Pirate_Roberts wrote...
The Joblist is stationary. The Timesheet is copied from a master
on the
server to a personal directory on the same server.

On 4 machines this worked just fine. On 2 others it doesnt work.

Do all 6 of these macines have the same Excel version and the same
drive mappings to network shares?

There are two workbooks involved. One should be G:\Office\Joblist.xls,
while the other is as yet unspecified. Is it also stored in G:\Office
until copied to individual users' T:\MyDocuments directories?

By details, I mean what exactly is the initial full pathname of the
file containing the formulas referring to G:\Office\Joblist.xls.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!

Well I finally figured it out.....Thanks for all the suggestions.

Seems that I must do a "save-as" into each persons directory rather than
copying it. There must be a system variable within excel/spreadsheet that is
redirecting the links.

Does anyone know why I need to save-as rather than copy?

Michael

"hrlngrv - ExcelForums.com" wrote:

Dread_Pirate_Roberts wrote...
The Joblist is stationary. The Timesheet is copied from a master
on the
server to a personal directory on the same server.

On 4 machines this worked just fine. On 2 others it doesnt work.

Do all 6 of these macines have the same Excel version and the same
drive mappings to network shares?

There are two workbooks involved. One should be G:\Office\Joblist.xls,
while the other is as yet unspecified. Is it also stored in G:\Office
until copied to individual users' T:\MyDocuments directories?

By details, I mean what exactly is the initial full pathname of the
file containing the formulas referring to G:\Office\Joblist.xls.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!