Excel 2007 : VLOOKUP losing path to one of 2 external links

[greatday2882]

I have found so many similar situations like mine, but nothing that has helped me. I bet its just a check box that i am overlooking, but here are the details:

We have a submittal file that we all share in the office which resides on our network mapped "Z:\" drive. The path is as follows:

Z:\CAD-TOOLS\CAD_Custom\Templates\Submittal.xlsb

In this file, i have an "Info" tab that references a job database to fill in the address, job number, customer information, etc. for the cover sheets and table of contents. The file that is referenced is located at:

Y:\_Projects\Jobs2.0.xlsm

The next tab is the "Table of Contents", which has a column to enter an 8-digit part number, and 4 columns with VLOOKUP formulas that populate from that part number. That File is located at:

Z:\CAD-TOOLS\CAD_Custom\Databases\Equipmaster.xlsm

We have been copying Submittal.xlsb and pasting it into our job folders when we need it. There are 3 drives with job files where we paste Submittal.xlsb, the H:\ K:\ and Y:\ drives. There are 2 out of the 8 computers in our department that are having the following problem, the other 6 are fine. If i were to copy Submittal.xlsb and paste it to a folder on the H:\ drive and then open it, click the Data tab, click edit links, the 2 links are shown as follows:

H:\CAD-TOOLS\CAD_Custom\Databases\Equipmaster.xlsm
Y:\_Projects\Jobs2.0.xlsm

If i were to copy Submittal.xlsb and paste it to a folder on the K:\ drive and then open it, click the Data tab, click edit links, the 2 links are shown as follows:

K:\CAD-TOOLS\CAD_Custom\Databases\Equipmaster.xlsm
Y:\_Projects\Jobs2.0.xlsm

If i were to copy Submittal.xlsb and paste it to a folder on the Y:\ drive and then open it, click the Data tab, click edit links, the 2 links are shown as follows:

Y:\CAD-TOOLS\CAD_Custom\Databases\Equipmaster.xlsm
Y:\_Projects\Jobs2.0.xlsm

Here is the typical formula (before copying to another drive) that looks to Equipmaster:

=VLOOKUP(D19,'Z:\SIM-TOOL\SG_Custom\Databases\[Equipmaster.xlsm]Equipmaster'!$A:$AZ,2,FALSE))

Here is the typical formula (this stays static even after copying to another drive) that looks to Jobs2.0:

=IF(C9="","",VLOOKUP(C9,'Y:\_Projects\[Jobs2.0.xlsm]Jobs'!$B:$BD,9,FALSE))

is there something i am missing? Thank you in advance for your help!

[dilipandey]

Hi Greatday2882,

I am not able to understand your query... is it the change of drive Y to K which is worrying u?

Regards,
DILIPandey

<click on below 'star' if this helps>

[greatday2882]

The file Submittal.xlsb is copied and pasted by everyone in the our department. Its located on the Z:\ drive. If you open it while its on the Z:\Drive, the 2 links are as follows:

Z:\CAD-TOOLS\CAD_Custom\Databases\Equipmaster.xlsm
Y:\_Projects\Jobs2.0.xlsm

If i copy Submittal.xlsb fromthe Z:\ drive to the H:\ drive, the 2 links are as follows:

H:\CAD-TOOLS\CAD_Custom\Databases\Equipmaster.xlsm
Y:\_Projects\Jobs2.0.xlsm

The First link changes to what ever drive i paste Submittal.xlsb to. The 2nd link stays static. That is what is confusing me.

[dilipandey]

Ok..

As a option, just check :-
Does the file Jobs2.0.xlsm is existing on H: drive as well? It's not and this may be why the link is static to Y drive (where the file currently housed). Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[greatday2882]

thank you for the responses dilipandey

The Jobs2.0.xlsm never leaves the Y:\ drive, and Equipmaster never leaves the Z:\ drive. We only copy the one file, Submittal.xlsb from the Z:\ drive to what ever location we need it.

[dilipandey]

Ok..

this is really strange.. ideally the link to original file (original drive) remains same - no matter where the file having link, getting copied unless the destination drive too has the similar file (which is linked in the file getting copied) and then it automatically refreshed the link to locally housed file there.

Regards,
DILIPandey

<click on below 'star' if this helps>

[greatday2882]

thanks again for your responses, but it seems that if i want to have an excel document that references 2 other excel files, that file MUST be stored on a different drive than the excel files i am referencing, or the path to the excel file that i am referencing becomes dynamic, and changes itself, along with all of the formulas within the work book.

[dilipandey]

yes..

I also work on some files where I need to work on some excel models which has the links to many files some in same folder and some in other drives..
and you are correct... links on the files which are on other drives remains unchanged.. but the links of local files changes...

Regards,
DILIPandey

<click on below 'star' if this helps>