Can a cell have a drop down list and can also be auto populated

[Guest]

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?

 

[Jason Morin]

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