Problems using INDIRECT formulae

Hello,


I am trying to reference another spreadsheet, using INDEX but I want to change the specific sheet the formulae refers to depending on what the user selects.
My sheet has a drop down list with all the sheet names, so the person inputs a persons name, and selects the sheet it comes from.
The sheets names refer to the home they work in.
So for sheet AL, this is what I get, which is perfect for my purpose. It simply returns the date they completed a training course.

=INDEX('[Training Matrix East Region.xls]AL'!$A$49:$BF$110,MATCH(B14,'[Training Matrix East Region.xls]AL'!$A$49:$A$110,0),MATCH($H$9,'[Training Matrix East Region.xls]AL'!$A$49:$BF$49,0))

Can someone advise if I can utilise INDIRECT to replace AL with the cell E14 where the sheet is being selected?
I've tried INDIRECT just on AL'! but can't get that to work.
I've also tried taking all parts of the formulae out into separate cells and using INDIRECT to rebuild it into a formulae but can't get it to work.

Any guidance would be greatly appreciated.
Thank you
Phil Bruton

Try

=INDEX(INDIRECT("'[Training Matrix East Region.xls]" &Sheet &"'!$A$49:$BF$110"),MATCH(B14,INDIRECT("'[Training Matrix East Region.xls]" &Sheet &"'!$A$49:$A$110"),0),MATCH($H$9,INDIRECT("'[Training Matrix East Region.xls]" &Sheet &"'!$A$49:$BF$49"),0))

where Sheet (named range) is your tab i.e E14

Thanks, I've tried your suggestion and it doesn't work. Returns #REF!

Worked OK for me. I even created your file "Training Matrix East Region.xls" to test it. This file needs to be OPEN as INDIRECT does not work with CLOSED files.