conditional dropdown

[aditya]

i want to make conditional dropdown like this

if a1="x" dropdown in a2 should be US,CANADA,MEXICO
if a1="y" dropdown in a2 should be CHINA,JAPAN,INDIA,KOREA


thank you in advance

 

[OssieMac]

Somewhere on your worksheet you can create a list of US,CANADA,MEXICO and
select the list then Define a name as x.

Create another list of CHINA,JAPAN,INDIA,KOREA and define name as y.

In cell A2 create a data validation dropdown list. For the list insert
=INDIRECT(A1).

 

[Roger Govier]

Hi

Take a look here
http://www.contextures.com/xlDataVal15.html

 

[aditya]

dear OssieMac,
i am not getting it.
please let me know
(1) how to create a list and define a name to it.
(2)in data validation dropdown list on putting =INDIRECT(A1) it shows
"source currently evaluate to an error message"

pls help.

 

[OssieMac]

What version of Excel are you using? I will then give you step by step
instructions but I will not be able to do so until tomorrow (12 hrs or so).

 

[Eduardo]

Hi,
Take a look at Debra web she has an excellent explanation step by step

http://www.contextures.com/xlDataVal02.html

 

[Sanjay]

Hi There,

Your question:-

if a1="x" dropdown in a2 should be US,CANADA,MEXICO
if a1="y" dropdown in a2 should be CHINA,JAPAN,INDIA,KOREA

This can be done by VBA.

Step 1: Enter first three cities in one coloumn, take an example it is in
cell F4 to F6
F4 = "US"
F5 = "CANADA"
F6 = "MAXICO"
(Note:- You are doing this stuff in Sheet1, default sheet)
Step 2: Go to VBA, open the Project Window, and then click the Sheet1 to
open its code window.
Step3: copy below code and try out
Note:- Make sure that you entered three cities in F4, F5, and F6 cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ObjCell As Range
Dim ObjDataRangeStart As Range
Dim ObjDataRanceEnd As Range

If Target.Row = 2 And Target.Column = 1 Then

Set ObjCell = ActiveSheet.Cells(1, 2)

Set ObjDataRangeStart = ActiveSheet.Cells(4, 6)
Set objDataRangeEnd = ActiveSheet.Cells(6, 6)

With ObjCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=" & ObjDataRangeStart.Address & ":" &
objDataRangeEnd.Address
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "Warning"
.ErrorMessage = "Select from list"
.ShowError = True
End With

End If

End Sub



Step 4: Press Ctrl + S to save your written code, and then go back to Excel.
Step 5: Now core part is done, click the cell A1 and enter your value
Note: Enter "x" in A1 cell and hit enter, it will show u the list of cities
in a dropdown list of A2 cell.



Good luck,
Sanjay