DV Lists that update each other
 

I have two DV lists. What I would like is for the second list to
update its value based on the selection in the first list, and vice
versa.

The reason for this is I'd like to be able to select something based on
its name, or based on its corresponding number. If I select based on
name, I'd like the number in the adjacent cell to update. If I select
based on number, I'd like the name in the adjacent cell to update.

Does anyone know if this is possible, and how to implement this?

Thanks in advance!

DV Lists that update each other
 

Yep. You need to do the following:-

Data Validation Settings. Select List and then in the Range box
select a range rather than type a manual list.

Find a spare place on your spreadsheet to create a list of items for
your first DV list.
You need the second range for your second DVlist to be dynamic and
based on the contents of the value of the cell where DV1 list is held.
A1?

Would need to know the structure of the two lists and the link to be
able to formulate DV list 2.

Is this enough to go on?

Let me know if you require further help

Ant

DV Lists that update each other
 

You'll need a bit of VBA code to effect what you described.

If open to using a small program, try this example:

Put this list in E1:F4
Apple Tree
Arborvitae Shrub
Crabgrass Weed
Granite Rock

Then
Select A1

From the Excel main menu:
<data<validation
Allow: List
Source: =$E$1:$E$4
Click the [OK] button

Select B1

<data<validation
Allow: List
Source: =$F$1:$F$4
Click the [OK] button

Now you have the basic DVs set up, but they don't automatically change each
other.

NEXT:
Right-click on the sheet tab and select "View Code" to open the worksheet
module in the VBA editor.

Copy the below code and paste it into the module:

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap
'Prevent Excel from reacting to the changed value of the second drop down list
Application.EnableEvents = False

'Check if the active cell is the first Data Validation cell
If Not Intersect(Target, Range("A1")) Is Nothing Then
'Set the next DV cell's value
Range("B1").Value = WorksheetFunction.Lookup(Range("A1"), Range("E1:E4"),
Range("F1:F4"))

'Check if the active cell is the second Data Validation cell
ElseIf Not Intersect(Target, Range("B1")) Is Nothing Then
'Set the next DV cell's value
Range("A1").Value = WorksheetFunction.Lookup(Range("B1"), Range("F1:F4"),
Range("E1:E4"))
End If

ErrTrap:
'Allow Excel to react normally
Application.EnableEvents = True
End Sub
'--------end of code----------

That's it.....test the worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I have two DV lists. What I would like is for the second list to
update its value based on the selection in the first list, and vice
versa.

The reason for this is I'd like to be able to select something based on
its name, or based on its corresponding number. If I select based on
name, I'd like the number in the adjacent cell to update. If I select
based on number, I'd like the name in the adjacent cell to update.

Does anyone know if this is possible, and how to implement this?

Thanks in advance!