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?

Hi!

Try this:

X1 = APR-05

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

Biff

"SKY" wrote in message
...
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?

Great ! I subsitute the suggested formula and it works.

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

SKY



"Biff" wrote:

Hi!

Try this:

X1 = APR-05

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

Biff

"SKY" wrote in message
...
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?

You're welcome. Thanks for the feedback!

Biff

"SKY" wrote in message
...
Great ! I subsitute the suggested formula and it works.

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

SKY



"Biff" wrote:

Hi!

Try this:

X1 = APR-05

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

Biff

"SKY" wrote in message
...
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?