Vlookup using cell value as part of adress

[vaclavvesely]

Hi, is there any way how to use a cell value (Text) as a reference to a worksheet in another workbook? I need different Vlookups for specific data in rows, because they refer to another workbook with a lot of worksheets and i have names of all worksheets in one column, so I don't want to change every formula manually. Thanks

Something like:

VLOOKUP(A3,'\\Folder\[workbook.xlsm]&B3&'!$B:$D,3,FALSE)

 

[Fluff]

Does this need to work on closed workbooks?

 

[vaclavvesely]

Yes, would be great if it will be possible.

 

[Fluff]

In that case I'm not sure it's possible.
You could possibly use the INDIRECT function (although I'm not sure if that works with UNC addresses of just mapped drives) however it does not work on closed workbooks.

 

[Fluff]

Using indirect it would be like

=VLOOKUP(A3,INDIRECT("'\\Folder\[workbook.xlsm]"&B3&"'!$B:$D"),3,FALSE)