bi-directional dropdown boxes

[TheObstacleIsThePath]

I have a column of data that is selected using a dropdown box. The
value placed here is looked up by the adjacent column to display some
text corresponding to the first. --let me try to be more specific
for clarity -- When I select a diagnostic number in column A, the
corresponding text appears in column B.

There are times when I cannot remember the number for a particular
diagnosis, and want to instead use a dropdown list on column B which
would result in the number appearing automatically in column A.

I don't think this is possible without scripts. Can anyone get me
started on this? I'm thinking I need to put drop down boxes on both
columns and then have changes to A trigger an update to B, and B
trigger an update to A.

Can anyone get me started with an elegant solution?

 

[Tom Ogilvy]

Right click on the worksheet tab and select view code.

In the resulting module at the top, in the left dropdown select worksheet
and in the right dropdown select change (assumes you are using xl2000 or
later).

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

target will be reference to the cell that triggered the update.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
On Error GoTo ErrHandler
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
res = Application.VLookup(Target.Value, Worksheets("Data") _
.Range("A:B"), 2, False)
If Not IsError(res) Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = res
End If
ElseIf Target.Column = 2 Then
res = Application.Match(Target.Value, Worksheets("Data") _
.Range("B:B"), 0)
If Not IsError(res) Then
Application.EnableEvents = False
Target.Offset(0, -1).Value = Worksheets("Data") _
.Cells(res, 1).Value
End If
End If

ErrHandler:
Application.EnableEvents = True

End Sub

 

[TheObstacleIsThePath]

Thanks!!!!