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)