Reference to Worksheet name

[Guest]

I am doing financial summary report based on 12 months worksheets. I have
formula extracting data from each month worksheet. When copying formula from
month to month, I would need to substitute the worksheet name, like "APR-05"
to "MAY-05".

I want like to define the 12-month worksheet names on the summary page. So,
the formula can refer to the corresponding reference cell instead.

=IF(ISERROR(INDEX('Apr-05'!A:I,MATCH('2005
Summary'!A2,'Apr-05'!B:B,0),9)),0,INDEX('Apr-05'!A:I,MATCH('2005
Summary'!A2,'Apr-05'!B:B,0),9))

Is there a way to implement that?

 

[Biff]

Hi!

Try this:

X1 = APR-05

=IF(ISERROR(INDEX(INDIRECT("'"&X1&"'!A:I"),MATCH('2005
Summary'!A2,INDIRECT("'"&X1&"'!B:B"),0),9)),0,INDEX(INDIRECT("'"&X1&"'!A:I"),MATCH('2005
Summary'!A2,INDIRECT("'"&X1&"'!B:B"),0),9))

Biff

 

[Guest]

Great ! I subsitute the suggested formula and it works.

Thank you very much for your help! Have a wonderful day!

SKY

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Great ! I subsitute the suggested formula and it works.

Thank you very much for your help! Have a wonderful day!

SKY