dynamic named range
 

Dear all,

Hope you can help me with the following.

I need to create a range (Dynamic range).
As all of you are awa Insert-Name-Define...
However, the name of my range needs to be dependent on the entry of a
certain cell.

For instance: If I type in B1 "Mercedes", then I want the name of my named
range to be "Mercedes".
If later on I need to change this for any reason to "BMW", then I need the
name of my named range to change as well to "BMW"..

Hope you guys can help me out with this.

I am using Excel 2003

dynamic named range
 

Right click sheet tabview codeinsert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("b1").Address Then Exit Sub
On Error GoTo newone
With Range("a2:b22")
.Name.Delete
newone:
.Name = Target
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gadeyne Dries" <Gadeyne wrote in message
...
Dear all,

Hope you can help me with the following.

I need to create a range (Dynamic range).
As all of you are awa Insert-Name-Define...
However, the name of my range needs to be dependent on the entry of a
certain cell.

For instance: If I type in B1 "Mercedes", then I want the name of my named
range to be "Mercedes".
If later on I need to change this for any reason to "BMW", then I need the
name of my named range to change as well to "BMW"..

Hope you guys can help me out with this.

I am using Excel 2003

dynamic named range
 

Hello,

Name your Dynamic Named Range "MyRange" and use this code by right clicking
on your Sheet Name tab and choose "View Code" in here paste;


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
On Error GoTo InvalidName
'http://www.ozgrid.com/Excel/DynamicRanges.htm
Range("MyRange").Name = Target(1, 1)
End If

Exit Sub
InvalidName: MsgBox Target(1, 1) & " is not a valid name", vbCritical
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Gadeyne Dries" <Gadeyne wrote in message
...
Dear all,

Hope you can help me with the following.

I need to create a range (Dynamic range).
As all of you are awa Insert-Name-Define...
However, the name of my range needs to be dependent on the entry of a
certain cell.

For instance: If I type in B1 "Mercedes", then I want the name of my named
range to be "Mercedes".
If later on I need to change this for any reason to "BMW", then I need the
name of my named range to change as well to "BMW"..

Hope you guys can help me out with this.

I am using Excel 2003

dynamic named range
 

Oops, that does work :) Name the dynamic named range any name and duplicate
that name in B1, then right click on the sheet name tab and choose "View
Code" and in here paste;

Public strName As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
On Error GoTo InvalidName
'http://www.ozgrid.com/Excel/DynamicRanges.htm
Range(strName).Name = Target(1, 1)
End If

Exit Sub
InvalidName: MsgBox Target(1, 1) & " is not a valid name", vbCritical
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
strName = Target(1, 1)
End If
End Sub




--
Regards
Dave Hawley
www.ozgrid.com
"ozgrid.com" wrote in message
...
Hello,

Name your Dynamic Named Range "MyRange" and use this code by right
clicking on your Sheet Name tab and choose "View Code" in here paste;


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
ElseIf Target(1, 1).Address = "$B$1" Then
On Error GoTo InvalidName
'http://www.ozgrid.com/Excel/DynamicRanges.htm
Range("MyRange").Name = Target(1, 1)
End If

Exit Sub
InvalidName: MsgBox Target(1, 1) & " is not a valid name", vbCritical
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"Gadeyne Dries" <Gadeyne wrote in message
...
Dear all,

Hope you can help me with the following.

I need to create a range (Dynamic range).
As all of you are awa Insert-Name-Define...
However, the name of my range needs to be dependent on the entry of a
certain cell.

For instance: If I type in B1 "Mercedes", then I want the name of my
named
range to be "Mercedes".
If later on I need to change this for any reason to "BMW", then I need
the
name of my named range to change as well to "BMW"..

Hope you guys can help me out with this.

I am using Excel 2003