2

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.

robinCTS
  • 5,746
  • 14
  • 30
  • 37
Sherif R
  • 21
  • 1
  • 1
  • 4
  • 1
    What is the INDIRECT you tried? Also realize that INDIRECT requires that the file be open to work. – Scott Craner Sep 14 '17 at 13:35
  • 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 &") – Sherif R Sep 14 '17 at 13:42
  • This link https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display 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. – abraxascarab Sep 14 '17 at 14:40

2 Answers2

1

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 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. – Sherif R Sep 14 '17 at 13:55
  • 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 – Scott Craner Sep 14 '17 at 13:59
  • 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. – Sherif R Sep 14 '17 at 14:03
  • No the `C:\Documents\Costs\[Costing 2017.xls]` workbook must be open. The workbook that is referenced in that cell must be open. @SherifR – Scott Craner Sep 14 '17 at 14:04
  • 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. – Sherif R Sep 14 '17 at 14:24
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)
robinCTS
  • 5,746
  • 14
  • 30
  • 37