Dynamically changing the file path of source workbook in VLOOKUP

excel dynamic vlookup worksheet-function

15,547

Solution 1

You need to use a defined name:

Go to Formulas > Defined Names > Define Name
Enter Costing in the "Name:" field
Enter 'C:\Documents\Costs\[Costing 2017.xls]Sheet2'!A:D in the "Refers to:" field

Now the following formula allows you to dynamically change the file path by simply changing the defined name Costing (via Formulas > Defined Names > Name Manager).

=VLOOKUP(A2,Costing,4,FALSE)

Solution 2

The formula with INDIRECT would be:

=VLOOKUP(A2,INDIRECT('Master Sheet'!$A$2),4,FALSE)

But INDIRECT requires that the reference workbook be open or it will return an error.

15,547

Sherif R

Updated on May 25, 2022

Comments

Sherif R almost 2 years
I want to have a cell that contains a file path ('C:\Documents\Costs\[Costing 2017.xls]Sheet2'!A:D) that will be used in VLOOKUP functions in different worksheets throughout the rest of the workbook.

Currently, I have the file path in cell A2 of the sheet named 'Master Sheet' Thus the reference to that cell is.
```
='Master Sheet'!$A$2
```
In the following sheets (sheets 2 to 8), I use VLOOKUP to reference certain product numbers in the Costing 2017.xls workbook, returning the price. I can use VLOOKUP this way
```
=VLOOKUP(A2,'C:\Documents\Costs\[Costing 2017.xls]Sheet2'!A:D,4,FALSE)
```
and it works fine.

But I want to be able to change the file path without having to go and copy the formula through potentially more than 8 sheets.

I've tried using INDIRECT(), but I'm not getting the result I want.
- Scott Craner over 6 years
  
  What is the INDIRECT you tried? Also realize that INDIRECT requires that the file be open to work.
- Sherif R over 6 years
  
  I wanted to use INDIRECT to simply be able to refer to a cell and return the text that is contained in the cell. However, when I'm trying to test the function, I'm not getting the text back but just '!REF', such as INDIRECT(" & A2 &")
- abraxascarab over 6 years
  
  This link stackoverflow.com/questions/579797/… will show you how to open an excel workbook behind the scenes (or as best as that's possible) using vba. You can then do your vlookup using indirect and close the workbook when you're done. If you are using multiple workbooks, it may take a while to open each. What you are attempting potentially has a lot of overhead.
Sherif R over 6 years

Thanks for your answer Scott. When I try just putting =INDIRECT('Master Sheet'!$B$2) into a cell it still returns a #REF!. I would like it to return the text in that cell, not open or do anything to that workbook.
Scott Craner over 6 years

You can't. You CANNOT use INDIRECT to reference a closed workbook. There is no way around it. So you either open the workbook you are referencing and use INDIRECT, or you change all the formulas individually. @SherifR
Sherif R over 6 years

The workbook is open though. I'm using =INDIRECT('Master Sheet'!$B$2) in the same workbook 'Master Sheet' is located. I just want to retrieve the text in that cell and use it in a formula.
Scott Craner over 6 years

No the C:\Documents\Costs\[Costing 2017.xls] workbook must be open. The workbook that is referenced in that cell must be open. @SherifR
Sherif R over 6 years

Well that puts a dent in my method, Thanks a lot Scott, I really appreciate the time and help. I'll try to figure out a different method.