Excel name box

QuestionEdit

I have this doubt regardingname box and defining. Suppose I have written ¡°Animals¡± in cell A1. From cells A2:A7 I write the names of the animals like ¡°cat¡±,¡±dog¡±.¡±elephant¡±,¡±tiger¡±,¡±lion¡±,¡±leopard¡±. Is there anyway that when I enter the animals name in cells A2:A7, the cells A2:A7 gets automatically defined as ¡°Animals¡± by copying thein cell A1 and using it as the title for the name box. I know t usual method that you have to select the cells A2:A7 and type ¡°Animals¡± in the name box but is there anyway to get it done automatically.. Please do reply¡­.

AnswerEdit

Making anything happen "automatically" ingenerally requires use of Visual Basic for Applications coding (vba, or "macros"). If you don't understand vba, then you can't accomplish this task.

If you do understand vba, then use the code below. It will use the value in cell A1 as the range name, and apply that name to the list of non-blank items starting in A2.

Sub nameTheRange()

   Dim rngName As Range, rngItems As Range
   Set rngName = Range("a1")
   Set rngItems = Range("a2")
   If Application.CountA(rngName.Range("a1:b2")) = 2 Then
       Set rngItems = rngName.CurrentRegion.Columns(1)
       ThisWorkbook.Names.Add rngName, _
           Intersect(rngItems, rngItems.Offset(1))
   End If

End Sub

QuestionEdit

I have this doubt regardingname box and defining. Suppose I have written ¡°Animals¡± in cell A1. From cells A2:A7 I write the names of the animals like ¡°cat¡±,¡±dog¡±.¡±elephant¡±,¡±tiger¡±,¡±lion¡±,¡±leopard¡±. Is there anyway that when I enter the animals name in cells A2:A7, the cells A2:A7 gets automatically defined as ¡°Animals¡± by copying thein cell A1 and using it as the title for the name box. I know t usual method that you have to select the cells A2:A7 and type ¡°Animals¡± in the name box but is there anyway to get it done automatically.. Please do reply¡­.

AnswerEdit

I have half what you need

You can set up a "predefined name" and set it up to grow or shrink depending on how many subcategories you type in instead of using A2:A7 on the refer to field, go an type in the following: =OFFSET(Sheet4!$A$1,1,0,COUNTA(Sheet4!$A:$A)-1,1) (change the sheet as you like.) what it will do is evaluate how many members are after the headings and create a range base on that number.

on the bad side, you can't have the name changed automatically. You can't do it via VB given that there is no Before_Change Event, and that you will need the previous name to call that name out and change it (technical stuff)