Crew List, duplicated values based on dropdown validation

Good Afternoon,

I'm having some problems creating a crew list, i have attached the base spreadsheet "crew august 5", i have highlighted in Blue where i wanted to pace the validation, and i have the employee list in column M - I have tried placing the Custom validation below but it still allows me to type duplicates.

=COUNTIF(D5:J13,D5)=1

I think this was a bust so i moved on



What i was trying to do was have a dropdown in each cell to select an employee but not allow any duplicates on vessels going forward, i tried to get around this by using the advice on this page (i have uploaded the spreadsheet Crews August to show how i have done this) - *Edit* i can't post links i'm told so i have edited this part out

I managed to get it count the row number by using

=IF(COUNTIF(D5:J13,P5)>=1,"",ROW())

From this i was able to create an unused employee list by using

=IF(ROW(P5)+1-5>COUNT(Q$5:Q$38),"",INDEX(P:P,SMALL(Q$5:Q$38,ROW(P5)+1-5)))

By this point i was quite happy with myself so i applied the validation to only use available crew members on the crew cells (highlighted in blue on the spreadsheet)

=OFFSET($Q$41,0,0,COUNTA($Q$41:$Q$74)-COUNTBLANK($Q$41:$Q$74),1)

But this is when it fell apart as the drop down list only worked in Alphabetical order ie. if i start by using Alan S and then Alan B it works, but if i start with lets say Carlos it won't remove him from the unused list

At this point i'm at a loss, i might be going about this the completely wrong way, or it might just be a simple matter of me incorrectly understanding the formulas/validation

I do apologise for the long winded post i sat there for a while and wasn't sure how best to word it so people understood me, any questions please let me know but i'm sure it's just me missing something simple.

Lee McAdam