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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"ssGuru" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sep 17, 1:32 pm, ssGuru <weca...@gmail.com> wrote:
>> On Sep 17, 9:54 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
>> 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" <weca...@gmail.com> wrote in message
>>
>> >news:(E-Mail Removed)...
>>
>> > >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
>
>
>
Similar Threads
