Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can a cell have a drop down list and can also be auto populated
I would like to have a cell that can be autopopulated based on the content of
a different cell but also have a drop down menu that can effect the 2nd cell. For example. Cell #1 is a part number that if known can be entered in and autopopulate cell #2 Cell #2 is a description of the part number, if you don't know the part number you can use the drop down list to search and have Cell #1 autopopulate with the info from Cell #2. Is this possible? |
#2
|
|||
|
|||
Assume your part numbers' range is defined as "partno"
and your part descriptions' range is defined as "partdesc". Set up your Validation lists in A1 and B1. Select A1, go to Data Validation, Allow: List, Source: =partno. Repeat for cell B1 with Source: =partdesc. Now right-click on the worksheet tab, select "View Code", and copy in the code below. Press ALT+Q and save the wb. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim PartNoRow As Long Dim PartDescRow As Long With Application If Intersect(Target, [A1]) Is Nothing Then GoTo FillA1 .EnableEvents = False With Target PartNoRow = Application.Match(.Value, _ Range("partno"), 0) .Offset(0, 1).Value = Range("partdesc") _ (PartNoRow).Value End With .EnableEvents = True Exit Sub FillA1: If Intersect(Target, [B1]) Is Nothing Then Exit Sub .EnableEvents = False With Target PartDescRow = Application.Match(.Value, _ Range("partdesc"), 0) .Offset(0, -1).Value = Range("partno") _ (PartDescRow).Value End With .EnableEvents = True End With End Sub --- HTH Jason Atlanta, GA -----Original Message----- I would like to have a cell that can be autopopulated based on the content of a different cell but also have a drop down menu that can effect the 2nd cell. For example. Cell #1 is a part number that if known can be entered in and autopopulate cell #2 Cell #2 is a description of the part number, if you don't know the part number you can use the drop down list to search and have Cell #1 autopopulate with the info from Cell #2. Is this possible? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|