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

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" <Pank@discussions.microsoft.com> wrote in message
news:D6453A5B-A953-4B00-8F70-5283BF22A388@microsoft.com...
> 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
> >
> >