Sheet name determined by cell value in INDEX/MATCH

[Shazbot]

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.

 

[Shane Devenshire]

Hi,

INDIRECT is the correct approach, however, it has one main drawback - it
fails when the external workbook is closed.

If there aren't too many sheets, you can use the CHOOSE function, something
like

CHOOSE(A1,'[Scorecard Source Data.xls]Unavailable by
Month'!$A$6:$A$65536,'[Scorecard Source Data.xls]Available by
Month'!$A$6:$A$65536)

In this case you would enter 1 in A1 and the first reference would be used,
if you enter 2 the second one would be used and so on.

Your formula is getting pretty long, if you are using 2003 you may run up
against the 1024 character limit, so I suggest you use range names in the
external workbooks. For example, if you named A6:A65536 UMonths then the
first reference shortens to [Scorecard Source Data.xls]UMonths or something
similar.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shazbot]

Hi,
I'm convinced this would work but I have indeed hit the 1024 barrier,
despite using short named ranges.
Good to know for future (shorter) formulas.

Thanks very much for your help.