How do I stop 2nd row deletion

[ssGuru]

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

 

[Jon Peltier]

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

 

[ssGuru]

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
_______






- 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.

Can you elaborate on the efficacy of using INDEX or OFFSET when
defining range names that will be used in calculations?

Will one allow the deletion of the first row while another won't?

Thanks, Dennis

 

[ssGuru]

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

 

[Jon Peltier]

Well, OFFSET is certainly easier, but you can use INDEX and INDIRECT
together. This gives you the range from A2 to the lowest cell in B (actually
from B2 to the lowest cell in A):

=INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)):INDIRECT("$B$2")

If you delete any single row, you're still fine (unless you delete row 2 and
it's the only row of data below the headers).

- Jon