Dynamic name ranges in the name

[rpaulson]

Hello all

I set up a dynamic name range using.
=OFFSET($A$1,0,0,COUNTA($A:$A),1)

The real kicker is that the Name itself I would like to be Dynamic, I would like to use the value in cell A1.

Anyone have an ideas?

Ross

 

[jasonb75]

Maybe something like (untested)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        ActiveSheet.Names.Add Name:=Range("A1").Value, RefersTo:= _
        "=OFFSET($A$1,0,0,COUNTA($A:$A),1)"
    End If
End Sub

As an afterthought, I'm not sure that making the name dynamic is a good idea, any existing formula using the old name will fail after the change is made.

 

[Aladin Akyurek]

Hello all

I set up a dynamic name range using.
=OFFSET($A$1,0,0,COUNTA($A:$A),1)

The real kicker is that the Name itself I would like to be Dynamic, I would like to use the value in cell A1.

Anyone have an ideas?

Ross

Also, what do you have in column A - just numbers or just text?

 

[rpaulson]

Column A has both number and text.

a VBA method would work but prefer not to.

 

[Aladin Akyurek]

Column A has both number and text.

a VBA method would work but prefer not to.

A1: FAD
A2: 5
A3: 8
A4:
A5: KAD

Given above, say on Sheet1, you want to define a dynamic named range with FAD in A1 as the name. Is this right?

 

[rpaulson]

Aladin Akyurek,

that is correct. But don't put too much time into it as a may approach the problem a different way.

Ross