excel - Reference range in multiple tabs of a different workbook -


i trying reference same range within multiple different tabs of workbook within index(match,match) setup.

i want 'tab_name" below reference text within cell represents tab name in other workbook..

     index('[workbook.xlsx]tab_name'!$a$1:$c$5,match(....),match(....)) 

so example in cell x1 have text 'sheet 1'. want 'sheet 1' appear in above formula in place of 'tab_name'. repeated many more tabs doing lookup (so cell x1 though x10 have 10 different tab names in other workbook).

i have tried following:

  index("'[workbook.xlsx]"&x1&"'!$a$1:$c$5",match(....),match(....)) 

and

indirect("'[" & a2 & "]" & a3 & "'!" & a4)   

where a2 workbook name, a3 tab name, , a4 range reference cell containing indirect formula within index.

for ease of simplicity excluded match(...) syntax follow same idea using index.

neither method appears work....i have reviewed documentation on indirect formula , read looks possible having trouble implementing.

have tried put them in array below?

=index(sheet1:sheet10!$b$1:$b$10,match(c1,sheet1:sheet2!$a$1:$a$10,0)) 

notice have sheets named there : between of them.


Comments