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

=iferror(index($q:$q,aggregate(15,6,row($q$5:$q$64)/($q$5:$q$64<>""),rows($q$5:$q5))),"")

Thanks very much Tim, smarter man then me, didn't even think of that

Thanks for all the help so far all, i have been asked to make one more change if possible, when filling in the holiday/training rows i need it to remove any crew/employees from the captain/mate/engineer/deckhand rows if duplicated.

As the above formula is a bit out of my league i'm not sure if this is going to be possible, is there anything you can suggest?

Lee

apply a source for them from P:P

Sorry Tim,

I realise i'm probably being a bit silly here, do you mean replace the data validation on them cells?