On Sep 17, 1:32 pm, ssGuru wrote:
On Sep 17, 9:54 am, "Jon Peltier"
wrote:





Define your names in terms of the first row. For example

Name: MyName
RefersTo:
=OFFSET(DATA1!$A$1,1,0,COUNTA(DATA1!$A:$A)-1,1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"ssGuru" wrote in message

oups.com...

I have a data sheet with a header row. DATA1
All my calculations in other sheets refer to rangeNames that start
with row2 in the DATA1 sheet.
I have found that any row can be deleted from the DATA1 sheet BUT if
row 2 deleted then the calculations fail.
I would like code that would allow row 2 fields to be edited or
corrected with PASTE Values but NOT allow row2 to be deleted.
Any ideas?
Dennis- Hide quoted text -

- Show quoted text -

Thanks Jon that is what I had done for a number of ranges using INDEX
rather than Offset which started the column range name at row 2 down
to how ever many records were in Data1, thus ignoring the header row.
For example Say "RangeNameH": =Calc1!$H$2:INDEX(Calc1!$H:
$H,COUNTA(Data1!$A:$A))

Works great in SUMPRODUCT formulas and such.

"...Maybe if I start at row 1 (=Calc1!$H$1:INDEX....) which is the
header
row then I could delete row2 without any problem??
But then I am concerned that the header row will be confused in the
calculations in some way..."

That won't work I found.

I tried to change the Ranges to include the header row and all my
SUMPRODUCT calculations failed. So I have to start the OFFSET or
INDEX range with row2 and then deletion of row2 looses the start and
all calculations endup with REF and VALUE errors. The calcs are happy
with the deletion of any row beyond row2.

I still need code that will PREVENT deletion of row2 but won't stop
editing or pasting data in the fields.

Thanks,
Dennis