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


.