Looking up in multiple sheets of another workbook

[imran1059]

Hi everyone,

Written below is my formula and yellow text in it is a sheet name in a workbook named as "3 - P&L BP monthly". In this work book I have multiple sheets from where data is extracted. All I want is to make the yellow text dynamic and give its reference to a specific cell let say A1. If value in A1 is changed then the formula should go to that sheet of the same work book and extract what is required.

=INDEX('[3 - P&L BP monthly.xlsx]Central (ISD & SISD)'!$D$3:$AA$18,MATCH($A41,'[3 - P&L BP monthly.xlsx]Central (ISD & SISD)'!$A$3:$A$18,0),MATCH(D$2,'[3 - P&L BP monthly.xlsx]Central (ISD & SISD)'!$D$2:$AA$2,0))


Thanks in advance for your help.
Imran,

 

[Fluff]

You can use INDIRECT, but the other workbook needs to be open or you'll get an error.

 

[imran1059]

You can use INDIRECT, but the other workbook needs to be open or you'll get an error.

please help me to use indirect here with cell A1 reference instead of yellow texts

 

[Fluff]

Try

=INDEX(INDIRECT("'[3 - P&L BP monthly.xlsx]"&A1&"'!$D$3:$AA$18"),MATCH($A41,INDIRECT("'[3 - P&L BP monthly.xlsx]"&A1&"'!$A$3:$A$18"),0),MATCH(D$2,INDIRECT("'[3 - P&L BP monthly.xlsx]"&A1&"'!$D$2:$AA$2"),0))

 

[imran1059]

Try

=INDEX(INDIRECT("'[3 - P&L BP monthly.xlsx]"&A1&"'!$D$3:$AA$18"),MATCH($A41,INDIRECT("'[3 - P&L BP monthly.xlsx]"&A1&"'!$A$3:$A$18"),0),MATCH(D$2,INDIRECT("'[3 - P&L BP monthly.xlsx]"&A1&"'!$D$2:$AA$2"),0))

its wonderful, Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback.