Linking files with variable directory path

[gase05]

To whom it may concern,

I am having trouble linking 2 files via vlookup function. The relevant
file structure is:
1. D:\NSW BURT Tool\1.BDM BURT files\1.Bus Unit Tracking Sheet.xls
2. D:\NSW BURT Tool\1.BDM BURT files\BUP Reference Files\BDM BURT_Last
Month.xls

If I move it into, for example, C:\My Documents (to become C:\My
Documents\NSW BURT Tool\.... etc), The information link from 1. to 2.
is maintained (i.e. under Edit>Links the file structure is C:\My
Documents\NSW BURT Tool\1.BDM BURT files\BUP Reference Files\2.
However, the link wher 2. needs to get info from 1. does not change. It
recognises the drive change, but does not recognise the added My
Documents folder. Under Edit/Links, the 2. file is trying to link to
C:\NSW BURT Tool\1.BDM BURT files\1.

A working example of the current formula is:

vlookup($a4,'D:\NSW BURT Tool\1.BDM BURT files\[1.]'!$C:$D,2,false)

I need the file structure to remain solid within the NSW BURT Tool
folder, regardless of which drive or what file structure is infront of
it. I.e. I need the "D:\" to be variable based on where NSW BURT Tool
is placed.

How can I do this?

I hope this makes some sense.

Thank you in advance for your assistance.

Regards

Peter

 

[Tom Ogilvy]

Excel doesn't support variable links to closed workbooks to any great
extent. Perhaps you could use the workbook_open event to update the
formulas based on the location of the file containing the formula.