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

--
Ian
--
"Hall" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

[Hall]

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

"Ian" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> "Hall" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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.

--
Ian
--
"Hall" <[email protected]> wrote in message
news:[email protected]...
> I'm not aware of any such function or construct.
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> --
>> "Hall" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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?
>> >
>> >
>>
>>
>
>