0

I am trying to build a dropdown list that will be used as a part of a concat that is in part used for a vlookup that retrieves info from an external excel workbook.

What I have at the moment is

=VLOOKUP(A2,CELL("contents",B2),2,FALSE)

where B2 has a concat of 3 parts: the folder location, the name of the file (which is used in the dropdown), and the extra info =CONCAT(A2,J3,K3)

'\\NETWORKNAME\FOLDER 1\[ + excel1 + .xlsx]Sheet1'!A1:E100

which successfully produces

'\\NETWORKNAME\FOLDER 1\[excel1.xlsx]Sheet1'!A1:E100

The leading quote is due to the folders having spaces in their name. Now my goal is that I'll use a dropdown list with the name of all the files and depending on the file name it will dynamically put together a VLOOKUP based on the chosen file name. I've tried so many different things but I just get #value, but when I just type the following it works fine:

=VLOOKUP(A2,'\\NETWORKNAME\FOLDER 1\[excel1.xlsx]Sheet1'!A1:E100,2,FALSE)
Donald Clark-Charest
  • 317
  • 1
  • 2
  • 6
  • Dynamic filepaths don't really work in formulas. You can use INDIRECT, but that requires that the target workbook be open. There may be some VBA workarounds. – BigBen Sep 23 '22 at 20:11
  • ANY time you are using functions or concatenation to create a variable reference, you are committed to using the `INDIRECT()` function. The INDIRECT function essentially says “let me give you a formula to create a dynamic string, and then hand that resulting string to the reference interpreter as if it was a hard-coded text string.” – Max R Sep 25 '22 at 05:19
  • You seem to be right, Ben. I have tried INDIRECT for the book having to be open explains why that also didn't work with the formula I tested. Same to you, Max. Thanks for that. I'll have to figure out another workaround for closed workbooks. – Donald Clark-Charest Sep 26 '22 at 14:47

0 Answers0