Forum Discussion
Substituting Text in Excel Lookup Formula
INDIRECT requires it to be text. And that solution is assuming the File names you are looking up are in the format WeekX.xlsx and the X is replaced by the value in cell C1. I'm also pretty sure this will require those workbooks to be open. If so try this:
=LOOKUP(7,INDIRECT("[Week"&$C$1&".xlsx]game! $A:$A"),INDIRECT("[Week"&$C$1&".xlsx]Game! $B:$B"))is is possible to do it all in the same workbook? you could also use power query to pull all the data from the other workbooks into a table in this workbook and then you can just do a look up in that table.
Change the formula to =LOOKUP(7,INDIRECT([Week&$C$1&.xlsx]game! $A:$A),INDIRECT([Week&$C$1&.xlsx]Game! $B:$B)).
Thanks for this suggestion. I entered the formula as suggested above, and I got an error message
and when pressing OK, I got this:
Apparently INDIRECT does not like the 'ref_text' to start with an "[". Any suggestions?
INDIRECT requires it to be text. And that solution is assuming the File names you are looking up are in the format WeekX.xlsx and the X is replaced by the value in cell C1. I'm also pretty sure this will require those workbooks to be open. If so try this:
=LOOKUP(7,INDIRECT("[Week"&$C$1&".xlsx]game! $A:$A"),INDIRECT("[Week"&$C$1&".xlsx]Game! $B:$B"))is is possible to do it all in the same workbook? you could also use power query to pull all the data from the other workbooks into a table in this workbook and then you can just do a look up in that table.
Thanks for this. How do I "accept" your solution in this app?
