INDEX Reference Error

[AYouQueTai]

Hello,

Can anyone explain why I'm getting a #Ref error with this formula
=INDEX("'"&D2&"'!$E$2:$E$300",MATCH(Roster[@Jersey],NJD[Jersey],0))
I'm trying to point to NJD's worksheet (which is entered in D2)

When I change it to it works fine.
=INDEX(NJD!E2:E300,MATCH(Roster[@Jersey],NJD[Jersey],0))

The problem is there are 32 teams and It needs to point to whatever abbreviation I have in D:D.

Thanks,

 

[AYouQueTai]

Ahh, had to add INDIRECT.
=INDEX(INDIRECT("'"&D2&"'!$E$2:$E$300"),MATCH(Roster[@Jersey],NJD[Jersey],0))

 

[AYouQueTai]

Sorry but is there a way to reference the table names and columns instead of a range?
=INDEX(NJD[Name],MATCH(Roster[@Jersey],NJD[Jersey],0))
In this case the 2-NJD should be replaced by whatever is in cell D2 as all teams have the same format of table and worksheet with their abbreviation.