Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
bi-directional dropdown boxes
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
bi-directional dropdown boxes
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 -- Regards, Tom Ogilvy " wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
bi-directional dropdown boxes
Thanks!!!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown boxes (box 1 result = box 2 | Excel Worksheet Functions | |||
How to copy Dropdown boxes? | Excel Programming | |||
dependent dropdown boxes | Excel Worksheet Functions | |||
Forms - Dropdown Boxes | Excel Programming | |||
AutoFilter Dropdown Boxes Missing | Excel Programming |