Hiding previously selected options in drop down list

[neerajkamat]

I wanted to hide/remove the already selected options in a dependent drop down list so that the next time i am left with only the remaining options.
Plzz help me out

[Glenn Kennedy]

Hi there. i think that this is what you want.

[neerajkamat]

That helped quite a bit. But still i am having problems as it is taking the first value of the full column and not adding the not selected option in the left named column. See if u could help me out.

[Glenn Kennedy]

I have repaired the changes that you made to my solution. It is now working as I had intended it to work. However, I do not think that it fully answers your question.

I have cleared out ALL the data. I have entered GH at C15 and D15. I have selected MH 06 AQ 1256 (4) at C8. That axle cannot be selected elsewhere in column C. That (I think) IS what you wanted. However, you can't select it in column D, either. I am certain that you DO NOT want that.

Please confirm.

[neerajkamat]

Thank you very much for your help, it's working perfectly fine and just as i wanted it to show.

[Glenn Kennedy]

If that is your problem solved, please mark the thread as solved and (preferably) say thanks by clicking the Add Reputation button at the foot of this post.

[newdoverman]

I attach this just for information. The Data Validation dropdown lists are shortened to so that there are no blanks at the end of the list. This uses named ranges to do this.

[Glenn Kennedy]

Newdoverman, I think that the OP will realise that your answer is EXACTLY what he wanted. However, I suspect that needing 20+ pairs of named ranges may prove a deterrent to him setting it up.