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

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

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

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