I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command. I can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works fine.
In my case the pathname will remain constant, but the workbook and sheet
will change. I tried the following two commands and neither works VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating the
full pathname for the file?