I have an INDEX/MATCH lookup formula:
=IF(ISERROR(IF(A7="","",1-INDEX('[Scorecard Source Data.xls]
Unavailable by Month'!$B$6:$IV$65536,MATCH($A7,'[Scorecard Source
Data.xls]Unavailable by Month'!$A$6:$A$65536,0),MATCH('G:\isosbsm\BST
Service\Reporting\Scorecards\Raw Data\[Scorecard Info.xls]Reference'!$G
$14,'[Scorecard Source Data.xls]Unavailable by Month'!$B$5:$IV$5,0)))),
1,IF(A7="","",1-INDEX('[Scorecard Source Data.xls]Unavailable by
Month'!$B$6:$IV$65536,MATCH($A7,'[Scorecard Source Data.xls]
Unavailable by Month'!$A$6:$A$65536,0),MATCH('G:\isosbsm\BST Service
\Reporting\Scorecards\Raw Data\[Scorecard Info.xls]Reference'!$G
$14,'[Scorecard Source Data.xls]Unavailable by Month'!$B$5:$IV$5,0))))

Where it references '[Scorecard Source Data.xls]Unavailable by Month'!
$B$6:$IV$65536 I want to be able to determine the sheetname based on a
cell value, so for example if I wanted to look at sheet '[Scorecard
Source Data.xls]Unavailable by Month UKFS' I could get the extra info
(UKFS) from another cell. I had a bash with INDIRECT but got nowhere!!

Is there a way to build a reference like this to use in this formula.

Apologies for the long formula and if the detail is a bit sketchy.

Thanks in advance.