Preventing the same choice being made in two drop-downs

[Meridian]

Hello - I hope someone can advise. I am building a template in Excel and I want an error message (or any message) to appear to the user if they select from a static drop-down the same option in column I as in column J (i.e. they have to pick something different in each case).

Issue is I can't exclude any options from the second validation because, essentially, it's the same list. So if, on one row, a person picks "Bill" in column I, they can't pick "Bill" in column J. However, if they have picked "James" in column I, they can pick "Bill" in column J.

Don't know if this is well explained enough but would appreciate any tips on this - I've tried messing about with validation, conditional formatting, putting an IF statement in the next column and so forth but I haven't found a method that isn't convaluted and easy for the user of the template...

 

[SteveO59L]

How have you created your "drop downs" ?

Data Validation, Controls Toolbox, Forms Toolbox ???????

 

[Meridian]

Hi Steve
Data Validation, Settings are Allow "List" and the Source is a named range on another worksheet.

 

[SteveO59L]

As a worksheet_change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Row = 1 And Target.Value = Target.Offset(0, 1).Value Then
MsgBox "Duplicate Selection not Allowed"
Target.ClearContents
End If
If Target.Column = 10 And Target.Row = 1 And Target.Value = Target.Offset(0, -1).Value Then
MsgBox "Duplicate Selection not Allowed"
Target.ClearContents
End If
End Sub

 

[Meridian]

Thanks, loads, Steve. Just one thing - how do I do this worksheet_change event? (I assume I then paste in the logic you've spelt out.)

 

[SteveO59L]

right click on the sheet tab and select the "view code" option. The paste the code into the right hand window

 

[jim may]

Steve - I'm not immediately seeing the use of the "And Target.Row = 1" portion of your code. It's probably something I'm not considering, but what?

TIA,
Jim

 

[SteveO59L]

just to make the test specific to certain cells.

I always have trouble getting the target.address to work correctly.

 

[jim may]

Doesn't this limit the code to ONLY a change (event) taking place ONLY in row 1?

 

[SteveO59L]

I would say that it excludes the code firing if you make changes anywhere else but I1 or J1.

As the OP didnt give the exact location of the data validation boxes, I have assumed them to be in row 1. Which of course could be wholly incorrect.