I have many named ranges that depend upon the number of records in a
"Prospects" worksheet which are used in a number of report
calculations.
For example the named range "WeightedNetRec" is defined by:
WeightedNetRec=Calc!$I$2:INDEX(Calc!$I:$I,COUNTA(P rospects!$A:$A))

The variable extent of the named range "Calc" will begin on row 2 and
extend depending upon the count of records in ColA, worksheet
"Prospects".
This is used to restrict the calculations that refer to the named
range "WeightedNetRec" to ignore all records beyond the limiting or
last record in Prospects.

This works just fine and has speeded up calculations that no longer
look down 65k rows in a named column range each time.

HOWEVER if someone deletes the first record in "Prospects" or cuts and
pastes another record over it my calculations fail with #REF and
#VALUE errors. If another record is copied and pasted over the 1st
record or it is just edited then no problems. All other rows in
"Prospects" can be deleted at will with no problems I understand how
this causes a problem since the range lost its first record but hey
there may be a record in 2, 3, 4... to take its place. Excel is happy
with deleting any other rows or pasting a copied row on top of row1.

Can anyone think of a way to block this ERROR so I don't loose the
formulas to a #REF or a change in the formula or named range that
would allow it to be happy if NO records are in "Prospects" or if
there are many records and the 1st one is deleted?

Thanks for your help,
Dennis

Try:

Calc!$I$2:INDEX(Calc!$I:$I,MAX(COUNTA(Prospects!$A :$A), 1))

Greg

"ssGuru" wrote:

I have many named ranges that depend upon the number of records in a
"Prospects" worksheet which are used in a number of report
calculations.
For example the named range "WeightedNetRec" is defined by:
WeightedNetRec=Calc!$I$2:INDEX(Calc!$I:$I,COUNTA(P rospects!$A:$A))

The variable extent of the named range "Calc" will begin on row 2 and
extend depending upon the count of records in ColA, worksheet
"Prospects".
This is used to restrict the calculations that refer to the named
range "WeightedNetRec" to ignore all records beyond the limiting or
last record in Prospects.

This works just fine and has speeded up calculations that no longer
look down 65k rows in a named column range each time.

HOWEVER if someone deletes the first record in "Prospects" or cuts and
pastes another record over it my calculations fail with #REF and
#VALUE errors. If another record is copied and pasted over the 1st
record or it is just edited then no problems. All other rows in
"Prospects" can be deleted at will with no problems I understand how
this causes a problem since the range lost its first record but hey
there may be a record in 2, 3, 4... to take its place. Excel is happy
with deleting any other rows or pasting a copied row on top of row1.

Can anyone think of a way to block this ERROR so I don't loose the
formulas to a #REF or a change in the formula or named range that
would allow it to be happy if NO records are in "Prospects" or if
there are many records and the 1st one is deleted?

Thanks for your help,
Dennis