VLookup using cell value to reference an external sheet

**AntonyInta** · 03-31-2014, 06:44 PM

Hello

Using the following formula
=IF(E35="","",VLOOKUP(E35,'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000,2,FALSE))
Works but is very inflexible

I would like to change the date leader in the external sheet filename to a cell value something along the lines of this
=IF(E35="","",VLOOKUP(E35,'S:\PB\[$A$1-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000,2,FALSE))
but no amount of tweaking seems to get it to work.

Any suggestions to get this to work would be awesome.

**wcymiss** · 03-31-2014, 07:22 PM

hi,
you can use function "indirect" like this:

=IF(E35="","",VLOOKUP(E35,indirect("'S:\PB\["&$A$1&"-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000"),2,FALSE))

but it can only work when you open the external sheet.

**AntonyInta** · 03-31-2014, 07:28 PM

Opening the external sheet isn't really an option.
The beauty of VLookup is that it works regardless of whether the sheet is open or not.
I was hoping to find a way to reference the current sheet using a cell value to make updating easier and less prone to mistakes.
Indirect works well if the referenced sheet is already open. I only want the active workbook open and not additional ones.