External References not working right.

[Dread_Pirate_Roberts]

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]

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" <[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Dread_Pirate_Roberts]

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?

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" <[email protected]>
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.

 

[Dread_Pirate_Roberts]

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?

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?

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" <[email protected]>
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.

 

[Dread_Pirate_Roberts]

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?

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?

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?

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" <[email protected]>
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]

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" <[email protected]>
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?

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?

=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?

:

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.

 

[Dread_Pirate_Roberts]

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.

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" <[email protected]>
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?

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?

:

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?

:

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.

 

[hrlngrv - ExcelForums.com]

Frank Kabel wrote..

this does not look like a valid formula. I'd guess it shoul 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 a
operator with the argument

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

an

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

which are range references on the same worksheet in the same workbook
s

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

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

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

On the other hand, when the file is closed, the former expressio
become

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

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

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

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

when the file's open an

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

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

 

[Frank Kabel]

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

 

[Dread_Pirate_Roberts]

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.

 

[Harlan Grove]

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]

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

 

[hrlngrv - ExcelForums.com]

Dread_Pirate_Roberts wrote..

The Joblist is stationary. The Timesheet is copied from a maste on th
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 sam
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:\Offic
until copied to individual users' T:\MyDocuments directories

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

 

[Dread_Pirate_Roberts]

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