CODEWITHSUNDEEP
exceldynamicvlookupworksheet-function
Sherif R
Sherif R

Reputation: 21

Dynamically changing the file path of source workbook in VLOOKUP

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.

Upvotes: 2

Views: 24427

Answers (2)

robinCTS
robinCTS

Reputation: 5886

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)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 1

Related Questions