Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do i set up conditional drop-down list
Hi All,
I have told create a Spred Sheet. Which has 2 inter-related Columns. Eg : Column A Should have Provision For Selecting a product from the drop Down list ( Product_1 , Product_2 ) And i have Other related Drop down list to Select from the Column B. ie. For Product_1 i should have Option_1,Option_2,Option_3,Option_4. and for Product_2 i should have Different drop-down list Select_1,Select_2. If any one have come across this please let me know how should i proceed. Thanks Vikas |
#2
|
|||
|
|||
Suppose you want to have your products in column A, and the options in B. Start with a defined list in say column M for products, O for options for product 1, P for product2 and so on. Keep N empty. Now, go to column A, Data Validation. Select List and add source as the list from column M. For column B, add source as list from column N (which is still empty). Now the object is, depending on the selection is column A, you need to fill the column N with its respective options. For this use the Worksheet_SelectionChange event, somthing like this. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set rngN = Range("N1:N2") Set rngO = Range("O1:O2") Set rngP = Range("P1:P2") If Cells(1, Target.Row) = "p1" Then Call myRng(rngN, rngO) Else Call myRng(rngN, rngP) End If End Sub Sub myRng(tRng, sRng) For i = 1 To tRng.Rows.Count tRng(i) = sRng(i) Next i End Sub The above code goes in the module of the sheet where you have your lists. Right click on the sheetname, select view code and enter the above code. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=381501 |
#3
|
|||
|
|||
Vikas,
Look at http://www.contextures.com/xlDataVal02.html Pank "mangesh_yadav" wrote: Suppose you want to have your products in column A, and the options in B. Start with a defined list in say column M for products, O for options for product 1, P for product2 and so on. Keep N empty. Now, go to column A, Data Validation. Select List and add source as the list from column M. For column B, add source as list from column N (which is still empty). Now the object is, depending on the selection is column A, you need to fill the column N with its respective options. For this use the Worksheet_SelectionChange event, somthing like this. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set rngN = Range("N1:N2") Set rngO = Range("O1:O2") Set rngP = Range("P1:P2") If Cells(1, Target.Row) = "p1" Then Call myRng(rngN, rngO) Else Call myRng(rngN, rngP) End If End Sub Sub myRng(tRng, sRng) For i = 1 To tRng.Rows.Count tRng(i) = sRng(i) Next i End Sub The above code goes in the module of the sheet where you have your lists. Right click on the sheetname, select view code and enter the above code. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=381501 |
#4
|
|||
|
|||
Thats a nice one.
Mangesh "Pank" wrote in message ... Vikas, Look at http://www.contextures.com/xlDataVal02.html Pank "mangesh_yadav" wrote: Suppose you want to have your products in column A, and the options in B. Start with a defined list in say column M for products, O for options for product 1, P for product2 and so on. Keep N empty. Now, go to column A, Data Validation. Select List and add source as the list from column M. For column B, add source as list from column N (which is still empty). Now the object is, depending on the selection is column A, you need to fill the column N with its respective options. For this use the Worksheet_SelectionChange event, somthing like this. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set rngN = Range("N1:N2") Set rngO = Range("O1:O2") Set rngP = Range("P1:P2") If Cells(1, Target.Row) = "p1" Then Call myRng(rngN, rngO) Else Call myRng(rngN, rngP) End If End Sub Sub myRng(tRng, sRng) For i = 1 To tRng.Rows.Count tRng(i) = sRng(i) Next i End Sub The above code goes in the module of the sheet where you have your lists. Right click on the sheetname, select view code and enter the above code. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=381501 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
Using symbols for drop down list boxes? | Excel Worksheet Functions | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions | |||
Drop List Referencing | Excel Worksheet Functions |