cross validation
 

I have two lists, one of color names, the other of color numbers. I need two
cells, each of which has one of these values, to cross reference themselves,
to only allow a matching color-number combination, and the cells need to be
dropdowns. I am guessing I need to do this with validation, but don't know
how to code the validation lists.

In other words, if a user selects from the color dropdown, the color number
dropdown needs become limited to only the matching number. If the user
selects the number, then the color name dropdown needs to limit to only the
color name. There is a one to one match between the colors and numbers, and
they'll be in a list side by side, for reference of the validation.

Thx for any help.
--
Boris

cross validation
 

Why not load the alternate automatically

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_COLOUR_RANGE As String = "A1"
Const WS_COLOUR_NUM_RANGE As String = "B1"
Dim tmp

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range(WS_COLOUR_RANGE)) Is Nothing Then
On Error Resume Next
tmp = Application.VLookup(.Value, Range("colours"), 2, False)
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_NUM_RANGE).Value = tmp
End If
ElseIf Not Intersect(Target, Me.Range(WS_COLOUR_NUM_RANGE)) Is
Nothing Then
On Error Resume Next
tmp = Application.Index(Range("colours").Columns(1), _
Application.Match(.Value, Range("colours").Columns(2),
0))
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_RANGE).Value = tmp
End If
End If

End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I have two lists, one of color names, the other of color numbers. I need
two
cells, each of which has one of these values, to cross reference
themselves,
to only allow a matching color-number combination, and the cells need to
be
dropdowns. I am guessing I need to do this with validation, but don't
know
how to code the validation lists.

In other words, if a user selects from the color dropdown, the color
number
dropdown needs become limited to only the matching number. If the user
selects the number, then the color name dropdown needs to limit to only
the
color name. There is a one to one match between the colors and numbers,
and
they'll be in a list side by side, for reference of the validation.

Thx for any help.
--
Boris

cross validation
 

I have no issue with using the worksheet event, but I am completely lost as
to what this thing is doing. Any chance you can translate the main points
into English? Just a little afraid of using it if I cannot understand it.

Thx if you can.
--
Boris


"Bob Phillips" wrote:

Why not load the alternate automatically

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_COLOUR_RANGE As String = "A1"
Const WS_COLOUR_NUM_RANGE As String = "B1"
Dim tmp

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range(WS_COLOUR_RANGE)) Is Nothing Then
On Error Resume Next
tmp = Application.VLookup(.Value, Range("colours"), 2, False)
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_NUM_RANGE).Value = tmp
End If
ElseIf Not Intersect(Target, Me.Range(WS_COLOUR_NUM_RANGE)) Is
Nothing Then
On Error Resume Next
tmp = Application.Index(Range("colours").Columns(1), _
Application.Match(.Value, Range("colours").Columns(2),
0))
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_RANGE).Value = tmp
End If
End If

End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I have two lists, one of color names, the other of color numbers. I need
two
cells, each of which has one of these values, to cross reference
themselves,
to only allow a matching color-number combination, and the cells need to
be
dropdowns. I am guessing I need to do this with validation, but don't
know
how to code the validation lists.

In other words, if a user selects from the color dropdown, the color
number
dropdown needs become limited to only the matching number. If the user
selects the number, then the color name dropdown needs to limit to only
the
color name. There is a one to one match between the colors and numbers,
and
they'll be in a list side by side, for reference of the validation.

Thx for any help.
--
Boris

cross validation
 

What is does is check whether cells A1 or B1 is being changed, A1 being the
colour name, B1 the number. If either is changed, it looks up the
corresponding value in a table named 'colours' and gets the corresponding
value.

You can easily change the target cells as they are constants at the head of
the code, and you can name the table from 'colours' to whatever you want.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I have no issue with using the worksheet event, but I am completely lost
as
to what this thing is doing. Any chance you can translate the main points
into English? Just a little afraid of using it if I cannot understand it.

Thx if you can.
--
Boris


"Bob Phillips" wrote:

Why not load the alternate automatically

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_COLOUR_RANGE As String = "A1"
Const WS_COLOUR_NUM_RANGE As String = "B1"
Dim tmp

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If Not Intersect(Target, Me.Range(WS_COLOUR_RANGE)) Is Nothing
Then
On Error Resume Next
tmp = Application.VLookup(.Value, Range("colours"), 2,
False)
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_NUM_RANGE).Value = tmp
End If
ElseIf Not Intersect(Target, Me.Range(WS_COLOUR_NUM_RANGE)) Is
Nothing Then
On Error Resume Next
tmp = Application.Index(Range("colours").Columns(1), _
Application.Match(.Value,
Range("colours").Columns(2),
0))
On Error GoTo 0
If Not IsError(tmp) Then
Me.Range(WS_COLOUR_RANGE).Value = tmp
End If
End If

End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"BorisS" wrote in message
...
I have two lists, one of color names, the other of color numbers. I
need
two
cells, each of which has one of these values, to cross reference
themselves,
to only allow a matching color-number combination, and the cells need
to
be
dropdowns. I am guessing I need to do this with validation, but don't
know
how to code the validation lists.

In other words, if a user selects from the color dropdown, the color
number
dropdown needs become limited to only the matching number. If the
user
selects the number, then the color name dropdown needs to limit to
only
the
color name. There is a one to one match between the colors and
numbers,
and
they'll be in a list side by side, for reference of the validation.

Thx for any help.
--
Boris

cross validation
 

There's a sample file here with cross validation:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0042 - Limit Data Validation
Selection' or 'DV0037 - Dependent Lists Country City'

BorisS wrote:
I have two lists, one of color names, the other of color numbers. I need two
cells, each of which has one of these values, to cross reference themselves,
to only allow a matching color-number combination, and the cells need to be
dropdowns. I am guessing I need to do this with validation, but don't know
how to code the validation lists.

In other words, if a user selects from the color dropdown, the color number
dropdown needs become limited to only the matching number. If the user
selects the number, then the color name dropdown needs to limit to only the
color name. There is a one to one match between the colors and numbers, and
they'll be in a list side by side, for reference of the validation.

Thx for any help.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html