Hi,

Is there code I can use instead of an entry in a cell to archive the
following.

I have a drop down list in a cell that is created from the menu item Data,
Validation.... and refers to a named range, this range name is defined with
a formula that varies on what text is in a particular cell, the formula is:
=IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$B $20). When Adm is in cell
A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything else,
the name range refers to Codes!$B$1:$B$20.

Thanks,

Rob

I hope I've read your post correctly.

This code will set your data validation list dependant on
the value in A1. Put the code in the module of the
worksheet that will contain the list:

'Assume data validation list goes in B1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ListRange As String

If Target = ActiveSheet.Range("A1") Then
If ActiveSheet.Range("A1") = "Adm" Then
ListRange = "=$C$1:$C$30"
Else
ListRange = "=$B$1:$B$21"
End If
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ListRange
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End Sub


-----Original Message-----
Hi,

Is there code I can use instead of an entry in a cell to
archive the
following.

I have a drop down list in a cell that is created from
the menu item Data,
Validation.... and refers to a named range, this range
name is defined with
a formula that varies on what text is in a particular
cell, the formula is:
=IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$ B$20).
When Adm is in cell
A1 my named range refers to Codes!$C$1:$C$30 whereas is
A1 is anything else,
the name range refers to Codes!$B$1:$B$20.

Thanks,

Rob


.

Assume the named range is List1

if SomeCondition then
worksheets("Codes").Range("C1:C30").Name = "List1"
else

worksheets("Codes").Range("B1:B30").Name = "List1"
End If

I don't know what condition sets the cell to ADM, so I can't guess what
condition to put in the code or to tell you where to put the code so it
updates the dropdown at the appropriate time. You could put it in the
SelectionChange event and test if the cell with the dropdown is what
triggered the event.

--
Regards,
Tom Ogilvy


"Rob" wrote in message
...
Hi,

Is there code I can use instead of an entry in a cell to archive the
following.

I have a drop down list in a cell that is created from the menu item Data,
Validation.... and refers to a named range, this range name is defined
with
a formula that varies on what text is in a particular cell, the formula
is:
=IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$B $20). When Adm is in
cell
A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything
else,
the name range refers to Codes!$B$1:$B$20.

Thanks,

Rob

Thanks Tod and Tom, I'll experiment over the weekend with the example.

Regards, Rob
"Tom Ogilvy" wrote in message
...
Assume the named range is List1

if SomeCondition then
worksheets("Codes").Range("C1:C30").Name = "List1"
else

worksheets("Codes").Range("B1:B30").Name = "List1"
End If

I don't know what condition sets the cell to ADM, so I can't guess what
condition to put in the code or to tell you where to put the code so it
updates the dropdown at the appropriate time. You could put it in the
SelectionChange event and test if the cell with the dropdown is what
triggered the event.

--
Regards,
Tom Ogilvy


"Rob" wrote in message
...
Hi,

Is there code I can use instead of an entry in a cell to archive the
following.

I have a drop down list in a cell that is created from the menu item
Data,
Validation.... and refers to a named range, this range name is defined
with
a formula that varies on what text is in a particular cell, the formula
is:
=IF(Menu!$A$1="Adm",Codes!$C$1:$C$30,Codes!$B$1:$B $20). When Adm is in
cell
A1 my named range refers to Codes!$C$1:$C$30 whereas is A1 is anything
else,
the name range refers to Codes!$B$1:$B$20.

Thanks,

Rob