Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
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). -- Regards, OssieMac "aditya" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
Hi
Take a look here http://www.contextures.com/xlDataVal15.html -- Regards Roger Govier "aditya" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional 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. "OssieMac" wrote: 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). -- Regards, OssieMac "aditya" wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
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. aditya;357594 Wrote: 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" wrote: 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). -- Regards, OssieMac "aditya" wrote: 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 -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100308 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
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). -- Regards, OssieMac "aditya" wrote: 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" wrote: 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). -- Regards, OssieMac "aditya" wrote: 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
Hi,
Take a look at Debra web she has an excellent explanation step by step http://www.contextures.com/xlDataVal02.html "aditya" wrote: 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" wrote: 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). -- Regards, OssieMac "aditya" wrote: 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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
conditional dropdown
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 "Simon Lloyd" wrote: 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. aditya;357594 Wrote: 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" wrote: 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). -- Regards, OssieMac "aditya" wrote: 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 -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100308 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional dropdown | Excel Discussion (Misc queries) | |||
How to have conditional dropdown | Excel Worksheet Functions | |||
Dropdown box display only data dependent on another dropdown box? | Excel Worksheet Functions | |||
How can I use conditional formating in dropdown using named list? | Excel Discussion (Misc queries) | |||
offer dropdown options based on another dropdown | Excel Discussion (Misc queries) |