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.

 

[Simon Lloyd]

Simply create your 2 lists of names, lets say on a new worksheet in
column A you put your first 3 countries then in column B enter your
other 4 countries, now highlight the list in column A, look to the top
left above column A you see a name box that has A1 in it (assuming your
list starts at A1) type List1 in there then hit return, do the same for
column B call it List2, now follow OssieMac's instructions for creating
your validation in A2 of the sheet you require the validation on.....in
A1 type List1, now A2 will be populated with your first list in a
dropdown.

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.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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