WHat is wrong with this formula   

=IF('11th game'!B(MATCH(A2,'11th game'!B20:B34,0)+19)=Sheet1!A2,COUNTIF('11th 
game'!C25:N25,Sheet1!C1),)

11th game is a worksheet name.   Excel does not like '11th game'!B   The 
MATCH returns what I expect.   Any help  is appreciated.

date: Tue, 8 Jul 2008 15:54:01 -0700   author:   Sportinus

Re: WHat is wrong with this formula   

Try it this way:

=IF(INDIRECT("'11th game'!B"&MATCH(A2,'11th 
game'!B20:B34,0)+19)=Sheet1!A2,COUNTIF('11th  game'!C25:N25,Sheet1!C1),"")

Hope this helps.

Pete

"Sportinus"  wrote in message 
news:BF8B091A-9C4C-45E8-85B1-A13A7D59B188@microsoft.com...
> =IF('11th game'!B(MATCH(A2,'11th 
> game'!B20:B34,0)+19)=Sheet1!A2,COUNTIF('11th
> game'!C25:N25,Sheet1!C1),)
>
> 11th game is a worksheet name.   Excel does not like '11th game'!B   The
> MATCH returns what I expect.   Any help  is appreciated.

date: Wed, 9 Jul 2008 00:03:18 +0100   author:   Pete_UK

RE: WHat is wrong with this formula   

You can't form the address in this manner.  Excel is trying to pass the 
result of the Match function as an argument to the function '11th game'!B.
Try instead =if(index('11th game'!B:B,match( ... )+19) = ...

"Sportinus" wrote:

> =IF('11th game'!B(MATCH(A2,'11th game'!B20:B34,0)+19)=Sheet1!A2,COUNTIF('11th 
> game'!C25:N25,Sheet1!C1),)
> 
> 11th game is a worksheet name.   Excel does not like '11th game'!B   The 
> MATCH returns what I expect.   Any help  is appreciated.

date: Tue, 8 Jul 2008 16:07:01 -0700   author:   bpeltzer