Hi,

I don't see anywhere, what version of Excel you are using, 2007 does allow
full column reference in the situation where 2003 would return an error.

Another option is to use the code to enter the formula. For example, just
turn on the recorder, move to each cell with a unique formula and press F2,
Enter. This will record the code for entering the formula. You may want to
tweek the results, for example here is a code sample:

bot = Range("A14").End(xlDown).Row
Range("C14:C" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""CurrQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("C14:C" & bot) = Range("C14:C" & bot).Value
Range("D14:D" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""NextQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("D14:D" & bot) = Range("D14:D" & bot).Value
Range("E14:E" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""FutrQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("E14:E" & bot) = Range("E14:E" & bot).Value
Range("F14:F" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("F14:F" & bot) = Range("F14:F" & bot).Value
Range("G14:G" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!Q$2:Q$" & Bottom & ")"
Range("G14:G" & bot) = Range("G14:G" & bot).Value
Range("H14:H" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),--('Global Detail'!D$2:D$" & Bottom &
"<""""))"
Range("H14:H" & bot) = Range("H14:H" & bot).Value
Range("B14:B" & bot) = "=SUM(RC[1]:RC[4])"
Range("B14:B" & bot) = Range("B14:B" & bot).Value

Some of these formulas are fairly complex SUMPRODUCT formula working against
30,000 rows but this method works fairly fast, and allow me to covert all the
formulas to values which would otherwise slow down the speed of the
spreadsheet since there are about 3/4 of a million formulas in the file.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

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