Filter based on cell value and copy only relevant data

[jamesround86]

Hi

I'm looking to have a sheet with a drop down to select a 'crew' then from that a second drop down with individuals names from that crew. Once a full selection is made I'm looking for the sheet to reference a table on another worksheet, filter against certain multiple shift codes, and for the codes relevant for that individual, copy the date column only, into a section on the originating sheet.

I've tinkered with VBA to copy information from a table after filtering for certain multiple criteria so I'm familiar with that, but rather than specifying a AutoFilter Field like 1 or two, I actually want it to do the filter based on the name that was provided on the drop downs on the original sheet and I've no idea how to do this.

Also on previous iterations of this I've come across a problem where not all the codes I want to search against are present for certain individuals (as an example I want to search on codes ALD and ALN for Annual Leave Day or Night) and copy dates from both search criteria into a Annual Leave column on the original sheet but they might only have the ALD code, this ends up making the whole code process cease due to missing criteria and I need to find a way around this if possible.

I have attached an example sheet with random names as examples, the drop downs I've already created using a sheet with individuals data on, there's also a truncated version of the shift codes/dates. Once I get my head round the code I need I can adapt this to my original sheet. I appreciate there are more shift codes than I've given definitions for, but I have listed the ones relevant for this example.

If anything else is needed please let me know, and thanks for looking!