Using Indirect inside "Index - Match"

[sunilbsrv2k]

Hi All,

I have 2 workbooks; one has got data in multiple tabs and each tab has a different name.

The second one has only 2 tabs; second tab has a table in which first 2 columns contain info about the type, tab name.

from 3rd column, it will be month wise data like Jan-19, Feb - 19, Mar-19 etc.

I used Index-Match-Match to get data from first workbook into 2nd one. This works fine

Since, tab names change in first worksheet and I have to get data in 2nd workbook based on the tab name mentioned in column D, I am using Indirect(D2) or D3 etc , but getting #Ref error

This works fine:

=INDEX('[Workbook1.xlsx]P I'!$B$3:$J$21,MATCH($C2,'[

Workbook1​

.xlsx]P I'!$B$3:$B$21,0),MATCH(K$1,'[

Workbook1​

.xlsx]P I'!$B$3:$J$3,0))

However, when I replace P I with Indirect (D2) in the above formula, it gives error.

Request your help.

Thanks

 

[Misca]

I understood you're only putting the worksheet name in the function. Try using the whole address, not just a part of it in INDIRECT. Feel free to use TEXTJOIN etc. functions to create the whole address text to use inside of INDIRECT.

 

[sunilbsrv2k]

I understood you're only putting the worksheet name in the function. Try using the whole address, not just a part of it in INDIRECT. Feel free to use TEXTJOIN etc. functions to create the whole address text to use inside of INDIRECT.

I tried this as well. Unfortunately, this is not working.

I also tried creating new sheet in the source file itself. Astonishingly, when I write formula in a cell is just displaying the formula, instead of the value that was needed.

I mean, in a cell, I wrote : "'" & INDIRECT("D2") & "'" &"!E4"; but I got the result like: 'Pament Int'!E4

Hence, unable to achieve the required results. Please help

​

 

[Misca]

Try:

=INDIRECT("'"&D2&"'!E4")

 

[DanteAmor]

Try this

=INDEX(INDIRECT("'[workbook1.xlsx]"&D4&"'!$B$3:$J$21"),MATCH($C2,INDIRECT("'[workbook1.xlsx]" & D4 & "'!$B$3:$B$21"),0),MATCH(K$1,INDIRECT("'[workbook1.xlsx]" & D4 & "'!$B$3:$J$3"),0)) <tbody> </tbody>

<tbody>
</tbody>