Deleting rows without effecting array formula

[robkrouse]

In this sheet, i've set it up to filter against negative keywords. As i go through and eliminate names with certain keywords, i delete the row. What it ends up doing is screwing up the cell reference part of the array.

Is there a way to make this not happen?

I use the sheet on a daily basis. Since the cell references get jacked up, it stops picking up keywords it should, as i delete cells.

I'm trying to eliminate the rows, so as i widdle it down, the time to process gets quicker. Make sense?

I've thought about color coding the cells i've already put eyes on, but it wouldn't solve the time issue.

Hope someone can help.

[Pete_UK]

It should be okay as long as you don't delete the first or last row of the range in the array formulae (though it does depend on the actual formula - if you are using INDIRECT, for example, then the range references won't change as they are text values). Perhaps you can change the ranges in your array formulae so that they begin at the row before the first row of data, and end on the row after the last row of data.

Hope this helps.

Pete

[Tony Valko]

Post the formula.

[robkrouse]

=SUM((TRIM(MID(SUBSTITUTE(" "&'Newest Version'!$A2," ",REPT(" ",100)),100*ROW(INDIRECT("1:"&LEN('Newest Version'!$A2))),100))=filter!$A$1:$CV$1)*1)