Data Validation allowing items from one list but not allowing items from another list.

[staljunk]

Is there a way to setup a data validation that would allow any item from one list, but would not allow any item from a second list? The column I am talking about is for Carrier Names. The Carrier Names list is always changing so they need to be able to add to this list, but I don't want anyone to be able to add a name that is contained in our Supplier list to the Carrier Name list.

Thanks.

[newdoverman]

You can do this with Data Validation.

In Data Validation, Allow CUSTOM, enter a formula like this substituting your ranges for the first list that you don't want in the second list

Please Login or Register  to view this content.

In this example there is a list in A1:A10 and the new list is going to be in column B. Select column B and create the Data Validation rule. Now when entering data in column B, if that entry is in column A, it will not be allowed.

[staljunk]

When I put the formula in, I was still able to enter a supplier name in the carrier name field. I also have an auto-fill (through VBA) dropdown in the carrier name column that no longer is there when I enter the countif formula. Below is what I entered.

=COUNTIF(Suppliers!$A$1:$A$1762,'Dispatch Carrier'!A1)=0

[newdoverman]

Ok, the formula that I should have given you is:

Please Login or Register  to view this content.

This works if you manually enter the values in column B but copying values into column B or entering via VBA will not be stopped.

You could use this formula on the worksheet to identify the incorrect entries and then filter them out for deletion:

Enter in column C and fill down.

Please Login or Register  to view this content.

A VBA solution to this I think is quite possible. Maybe a VBA expert will chime in.