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