Here is one of the formulas:
=IF(ISNA(MATCH(1,(RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$ C$127=$M$1),0)),"",INDEX(RTH!$F$1:$F$127,MATCH(1,( RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$C$127=$M$1),0)))

A127, C127 & F127 are the row numbers that are being reduced...


"Shane Devenshire" wrote:

Hi,

One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")
or
OFFSET(A1,0,0,1000)

it would be better if we knew the formula you were using.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Hello,
On sheet 1 I use array formulas that have a set row range. Example
(A1:A1000). Sheet 1 requests date from sheet 2.
On sheet 2 there is data imported from another application that leaves page
header info multiple times, and I use a "cleanup" formula to delete the rows
that contain the unwanted page header info.
I have found that the row deletion on sheet 2 changes the ranges in the
formulas on sheet 1. Example, If the cleanup formula deletes 10 rows on sheet
2, the formula range on sheet 1 changes from A1:A1000 to A1:A990. Everytime I
run it, the range is reduced further. This is the cleanup formula:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?
thanks,
Robert