Lookup file with relative pathname

[Hall]

My formulas such as VLOOKUP refer to cells in other workbook files.

Excel automatically uses the "hardcoded" absolute pathnames to these files.
I need to use a relative pathname so that the set of workbooks are portable.

For example, where I currently have
vlookup(A1,'N:\Groupname\projectA\workbook.xls'!B1:B10,1)

I want
vlookup(A1,'..\workbook.xls'!B1:B10,1)

Is this possible?

 

[Ian]

Can you use something like the Path property to retrieve the full path of
the current workbook then use this to construct the hard-coded pathname? I'm
not sure how to create the pathname within the vlookup argument, but this
may be a starting point for you.

eg fullpath = Workbooks("book1.xls").Path

 

[Hall]

I'm not aware of any such function or construct.

Can you use something like the Path property to retrieve the full path of
the current workbook then use this to construct the hard-coded pathname? I'm
not sure how to create the pathname within the vlookup argument, but this
may be a starting point for you.

eg fullpath = Workbooks("book1.xls").Path

 

[Ian]

I'm sorry. I misread your OP. The function I am referring to is in VBA. You
would need to write code to use this option. I don't know of any way to do
this with a formula.