Every month we create Excel project reports using data provided in an external workbook that always resides in the same relative folder position. I want to use VLOOKUP (lookup_value,table_array,col_index_num,range_lookup) but with a formula in "table_array" to obtain the necessary data without needing to open the other workbook.
I can use:
=LEFT(CELL("filename"),(FIND("[",CELL("filename"))-1))&"Finance Supplement\[Fin support data.xlsx]SSRS '!$A$2:$V$80"
to generate the full path to the array.
Is it possible to use this as an argument for table_array or am I breaking an obvious rule somewhere? I have tried all kinds of formats without success and I know Indirect() is an option but would prefer the other workbook not need to be opened.
Help really appreciated.
Bookmarks