Reference range in multiple tabs of a different workbook

1
votes

I am trying to reference the same range within multiple different tabs of another workbook within an index(match,match) setup.

I essentially want the 'Tab_name" below to reference text within a cell that represents a tab name in the other workbook..

     index('[workbook.xlsx]Tab_name'!$A$1:$C$5,match(....),match(....))

So for example in cell X1 I have the text 'Sheet 1'. I want 'Sheet 1' to appear in the above formula in the place of 'Tab_name'. this would then be repeated for many more tabs for which I am doing the lookup (so cell x1 though x10 would have 10 different tab names in the other workbook).

I have tried the following:

  index("'[workbook.xlsx]"&X1&"'!$A$1:$C$5",match(....),match(....))

and

INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)

where A2 is the workbook name, A3 the tab name, and A4 the range I then reference the cell containing the indirect formula within my index.

for ease of simplicity I excluded the match(...) syntax but it would follow the same idea I am using for the index.

Neither method appears to work....I have reviewed documentation on the indirect formula and from what I read it looks like this is possible but I am having trouble implementing.

1
excelexcel-formula
Can you detail what values A3 and A4 have? You can also use the "Evaluate Formula" to step through what values its populating before parsing the INDIRECT. What error message are you getting as a result of your formula? – nbayly
Is the workbook open? INDIRECT only works with external workbooks if they are open. Also, does that sheet name has a space in it? If so, you want to wrap it with single quotes. Finally, I would test the string you generate for the range outside of the INDIRECT to verify it is a valid range. – Byron Wall

1 Answers

0
votes

Have you tried to put them in an array like below?

=index(sheet1:sheet10!$B$1:$B$10,MATCH(C1,sheet1:sheet2!$A$1:$A$10,0))

Notice where I have sheets named there is a : between all of them.