software

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 quot;APR-05quot;
to quot;MAY-05quot;.

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(quot;'quot;amp;X1amp;quot;'!A:Iquot;),MATCH(' 2005
Summary'!A2,INDIRECT(quot;'quot;amp;X1amp;quot;'!B:Bquot;),0),9)),0,INDE X(INDIRECT(quot;'quot;amp;X1amp;quot;'!A:Iquot;),MATCH('2005
Summary'!A2,INDIRECT(quot;'quot;amp;X1amp;quot;'!B:Bquot;),0),9))

Biff

quot;SKYquot; gt; wrote in message
...
gt;I am doing financial summary report based on 12 months worksheets. I have
gt; formula extracting data from each month worksheet. When copying formula
gt; from
gt; month to month, I would need to substitute the worksheet name, like
gt; quot;APR-05quot;
gt; to quot;MAY-05quot;.
gt;
gt; I want like to define the 12-month worksheet names on the summary page.
gt; So,
gt; the formula can refer to the corresponding reference cell instead.
gt;
gt; =IF(ISERROR(INDEX('Apr-05'!A:I,MATCH('2005
gt; Summary'!A2,'Apr-05'!B:B,0),9)),0,INDEX('Apr-05'!A:I,MATCH('2005
gt; Summary'!A2,'Apr-05'!B:B,0),9))
gt;
gt; 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
quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; X1 = APR-05
gt;
gt; =IF(ISERROR(INDEX(INDIRECT(quot;'quot;amp;X1amp;quot;'!A:Iquot;),MATCH(' 2005
gt; Summary'!A2,INDIRECT(quot;'quot;amp;X1amp;quot;'!B:Bquot;),0),9)),0,INDE X(INDIRECT(quot;'quot;amp;X1amp;quot;'!A:Iquot;),MATCH('2005
gt; Summary'!A2,INDIRECT(quot;'quot;amp;X1amp;quot;'!B:Bquot;),0),9))
gt;
gt; Biff
gt;
gt; quot;SKYquot; gt; wrote in message
gt; ...
gt; gt;I am doing financial summary report based on 12 months worksheets. I have
gt; gt; formula extracting data from each month worksheet. When copying formula
gt; gt; from
gt; gt; month to month, I would need to substitute the worksheet name, like
gt; gt; quot;APR-05quot;
gt; gt; to quot;MAY-05quot;.
gt; gt;
gt; gt; I want like to define the 12-month worksheet names on the summary page.
gt; gt; So,
gt; gt; the formula can refer to the corresponding reference cell instead.
gt; gt;
gt; gt; =IF(ISERROR(INDEX('Apr-05'!A:I,MATCH('2005
gt; gt; Summary'!A2,'Apr-05'!B:B,0),9)),0,INDEX('Apr-05'!A:I,MATCH('2005
gt; gt; Summary'!A2,'Apr-05'!B:B,0),9))
gt; gt;
gt; gt; Is there a way to implement that?
gt;
gt;
gt;

You're welcome. Thanks for the feedback!

Biff

quot;SKYquot; gt; wrote in message
...
gt; Great ! I subsitute the suggested formula and it works.
gt;
gt; Thank you very much for your help! Have a wonderful day!
gt;
gt; SKY
gt;
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; X1 = APR-05
gt;gt;
gt;gt; =IF(ISERROR(INDEX(INDIRECT(quot;'quot;amp;X1amp;quot;'!A:Iquot;),MATCH(' 2005
gt;gt; Summary'!A2,INDIRECT(quot;'quot;amp;X1amp;quot;'!B:Bquot;),0),9)),0,INDE X(INDIRECT(quot;'quot;amp;X1amp;quot;'!A:Iquot;),MATCH('2005
gt;gt; Summary'!A2,INDIRECT(quot;'quot;amp;X1amp;quot;'!B:Bquot;),0),9))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;SKYquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am doing financial summary report based on 12 months worksheets. I
gt;gt; gt;have
gt;gt; gt; formula extracting data from each month worksheet. When copying formula
gt;gt; gt; from
gt;gt; gt; month to month, I would need to substitute the worksheet name, like
gt;gt; gt; quot;APR-05quot;
gt;gt; gt; to quot;MAY-05quot;.
gt;gt; gt;
gt;gt; gt; I want like to define the 12-month worksheet names on the summary page.
gt;gt; gt; So,
gt;gt; gt; the formula can refer to the corresponding reference cell instead.
gt;gt; gt;
gt;gt; gt; =IF(ISERROR(INDEX('Apr-05'!A:I,MATCH('2005
gt;gt; gt; Summary'!A2,'Apr-05'!B:B,0),9)),0,INDEX('Apr-05'!A:I,MATCH('2005
gt;gt; gt; Summary'!A2,'Apr-05'!B:B,0),9))
gt;gt; gt;
gt;gt; gt; Is there a way to implement that?
gt;gt;
gt;gt;
gt;gt;