Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula returning #N/A when inserting new rows.
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="Pl aying",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="Pl aying",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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula returning #N/A when inserting new rows.
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="Pl aying",ROW(Teams!$C$83:$C$ 106)),ROW(Teams!$A1))-1,4) since you won't be adding rows there. -- Regards, Tom Ogilvy wrote in message ups.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="Pl aying",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="Pl aying",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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula returning #N/A when inserting new rows.
Tom,
You are a gem. Thanks again Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Formula into Array | Excel Discussion (Misc queries) | |||
Array formula returning the cumulative sum | Excel Worksheet Functions | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
Array formula returning terminated employees | Excel Worksheet Functions | |||
Returning an Array as part of creating a cell formula | Excel Programming |