Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
Data validation with hyperlinks | Excel Worksheet Functions | |||
Copy workbook- Validation function | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) |