Calling up a dropdown based on the value of a cell

[SandsB]

User enters a value to cell A1.
Cell A2 becomes a drop down based on a named range. So if you enter "Mom" in A1 you get one drop down list in A2, if you enter "Dad" in A1 you get a different drop down list.
Possible?

 

[erik.van.geit]

Hi,

You could even put a validation list in A1.

take a look at this "famous" thread
see Aladins post
http://www.mrexcel.com/board2/viewtopic.php?t=40&start=26
in the mean time Aladin wrote something "new"
http://www.mrexcel.com/board2/viewtopic.php?t=267483
see also this explanation
http://www.contextures.com/xlDataVal02.html
and
http://www.mrexcel.com/board2/viewtopic.php?t=15030
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html
for a technique using comboboxes
http://www.mrexcel.com/board2/viewtopic.php?t=152281&start=5

kind regards,
Erik

 

[MickG]

Hi, Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
 [COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] Range("A2")
      [COLOR="Navy"]If[/COLOR] Range("A1") = "MOM" [COLOR="Navy"]Then[/COLOR]
        .Select
        .Validation _
            .Modify xlValidateList, xlValidAlertStop, _
            xlBetween, "=" & Range("B1:B10").Address & ""
     [COLOR="Navy"]ElseIf[/COLOR] Range("A1") = "DAD" [COLOR="Navy"]Then[/COLOR]
        .Select
       .Validation _
            .Modify xlValidateList, xlValidAlertStop, _
            xlBetween, "=" & Range("C1:C10").Address & ""
    [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick