I am trying to dynamically reference different worksheet names using the INDIRECT function, like this:
Column A: contains target sheet names
Column B: =INDEX(INDIRECT(A1&"!A:B"),MATCH("Data",INDIRECT(A1&"!B:B"),0),1)
Column A of target sheet: contains values I want formula to return
Column B of target sheet: contains the word "Data", used to indicate which row to pull data from
I would like to replace INDIRECT with a non-volatile function if possible. I have read that the CHOOSE function might be able to do this, but I'm unsure how to apply it to my scenario. Also note that my actual workbook contains 40+ sheets (and I need the flexibility to add or change sheet names easily) so the =CHOOSE(index_num,value1,value2,...) format doesn't really seem feasible.
Bookmarks