Drop Down List to Not Repeat Selected Value

[superdrno]

To lay down the foundation...I have 1 Workbook that contains 2 Sheets. Sheet 1 is where the drop down list will be located and Sheet 2 will hold the list that Sheet 1 will be referencing. Within Sheet 1 I have 2 separate columns, Column A that is for Dogs and a Column C that is Cats. Then in Sheet 2, I have Column A that has a list of different breeds of dogs and Column B that has a list of different types of cats. Also in Sheet 1, there is Column B in case the dog is a mixed breed and Column D in case the cat is a mix.

So far I've made it so that the drop down list in Sheet 1 Column A lists the different types of dogs in the list in Sheet 2 Column A, while in Sheet 1 Column C lists the different types of cats in the list in Sheet 2 Column B. What I'm trying to do is to have Columns B and D in Sheet 1 show the drop down list without repeating the selected value in Column A and C, respectively.

Attached is the document in case the description above was confusing. Thank you in advance for all of the help.

[MickG]

Try this in Sheet1 module.

Please Login or Register  to view this content.

Regards Mick

[superdrno]

So I tried applying this code/concept to my real worksheet and I'm having some issues.

So my cat/dog example Columns 1 and 2 and then Column 3 and 4 correspond to my real version as Column 4 and 5 and then Columns 7 and 8.
In addition Dog is called "Applicable Bus (1)" and Cat is called "Applicable Data (1)"

So I made the necessary changes in green but I run into an issue at the red text.

Please Login or Register  to view this content.

What am I doing in correctly? And how do i adjust it?

[MickG]

I not aware that you can create a Named Range like "Applicable Box (1)" with spaces in the Name.
If that's what the names refer too. !!!!! Please Expand on your Thinking !!

[superdrno]

I apologize I am not a frequent user of VBA and have to use it for this single occasion. Ok well I tried taking out the spaces and adding in underscores in the code and it still does not work. And so I changed it to simply "Business" and "Data" in the code and in the excel changed it to Business 1 Business 2 Data 1 Data 2 and I still have the same error. Is it the number of characters that's the issue? With "Dog" and "Cat" they were both 3 letter words so it worked perfectly.

Please Login or Register  to view this content.

[MickG]

Try this:-