Array formula returning #N/A when inserting new rows.

[annysjunkmail@tiscali.co.uk]

Hi,

Tom Ogilvy provided this excellent array formula for me a while ago
which looks up and returns data that contains the keyword "Playing" in
another worksheet. I have the formula in 5 columns and 11 rows. It
works great but it causes problems when I try to insert rows above the
formula, as it returns #N/A.

here is the formula...

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Playing",ROW($C$83:$C$105)),ROW($A1))-1,4)

If I insert a row above it recalulates the formula to...

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Playing",ROW($C$83:$C$106)),ROW($A1))-1,4)

Note that the first ROW has increased from $C$105 to $C$106.

Can someone help?

Thanks
Tony

[Tom Ogilvy]

since ROW is returning a row number, it didn't make any difference what
sheet it was on - so I referred to the sheet with the formula. However, to
avoid problem, refer to the lookup sheet like this:

=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Playing",ROW(Teams!$C$83:$C$
106)),ROW(Teams!$A1))-1,4)

since you won't be adding rows there.

--
Regards,
Tom Ogilvy



<annysjunkmail@tiscali.co.uk> wrote in message
news:1129323124.194136.96270@g49g2000cwa.googlegroups.com...
> Hi,
>
> Tom Ogilvy provided this excellent array formula for me a while ago
> which looks up and returns data that contains the keyword "Playing" in
> another worksheet. I have the formula in 5 columns and 11 rows. It
> works great but it causes problems when I try to insert rows above the
> formula, as it returns #N/A.
>
> here is the formula...
>
>
=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Playing",ROW($C$83:$C$105)),
ROW($A1))-1,4)
>
> If I insert a row above it recalulates the formula to...
>
>
=OFFSET(Teams!$A$1,SMALL(IF(Teams!$C$83:$C$105="Playing",ROW($C$83:$C$106)),
ROW($A1))-1,4)
>
> Note that the first ROW has increased from $C$105 to $C$106.
>
> Can someone help?
>
> Thanks
> Tony
>

[annysjunkmail@tiscali.co.uk]

Tom,
You are a gem. Thanks again
Tony