dynamic validation list under criteria

Try this file, which uses array formulas to extract the countries visited, and a dynamic named range to create the validation list.

Validation.xlsx

Is there a reason why you can't use the filters on the source data? Select the date you want, then the name, and the countries visited will be automatically shown.

If you can't do it that way, the only way I can see to do what you want is first to extract the multiple results into another range, then use that range as the source for a drop-down list.
Let's say you want to put the extracted list in column R. In R2 enter the following formula:

Formula:

Please Login or Register  to view this content.

It's an array formula, so enter it with Ctrl-Shift-Enter not just Enter. You'll know it's worked if you get curly brackets {} round the formula in the formula bar - don't try to enter them yourself.
You may need to replace the commas , with semi-colons ;
Obviously, replace 'no other countries' with either "" (blank) or your chosen text.
Drag this down as far as you want.

Then use Data Validation 'List' to refer to this newly-created range.
You may wish to consider making this range a Named Range and perhaps making it a dynamic range (search for 'dynamic range' for instructions on how to do that) - if you then use "" (blank) in the formula, all you'll get in the drop-down box is the countries.

Here's your file showing the above working, with '09/01/15' and 'Bill' selected, so giving two results ('Greece' and 'Denmark'): Validation _ for petrosgeorgilas _ AS.xlsx

Hope that's of some help.

Edit: Bernie's solution above is much the same as mine (a slightly different formula with the same result), but he's been kind enough to do the dynamic range for you too!

The formulas can work with thousands of rows - even tens or hundreds of thousands of rows. Since one of your filters is a date, and you are adding 10-20 per day, then the most returned lot numbers should be around 20 - easy for Excel.

You need to better describe your work process. When you enter values into K2:L2, then get a drop down on M2, when you select the value in M2, are you done with row 2? Why would you need as many rows in K:M as you have data? Wouldn't that just be replicating the data?

Try this formula in L2 and fill down as far as required.

Formula:

Please Login or Register  to view this content.

Array formula enter with Ctrl + Shift + Enter

This one will change the validation list as you fill in the columns, based on the last row K:L

Progressive Validation.xlsx

Replace the formula I gave you in post 3 with this amended version:

Formula:

Please Login or Register  to view this content.

Change each instance of $100 to be as large as you need ($1000, $10000 or even larger).
It's still an array formula entered with Ctrl-Shift-Enter but now you should drag it right not down.
I put the first formula in R2, which is important for the next step.

Now go to Name Manager and create a new name (I called it 'ListForColumnM') and in the 'Refers to' box, put this:
=OFFSET(Sheet1!$M2,0,5):OFFSET(Sheet1!$M2,0,15)
If you've put the first formula in a column other than R, you'll need to adjust the first offset (if S replace 5 with 6, if T replace 5 with 7, and so on). This will give you a ten-value drop-down list (in the next step) - if you think there will be more, then adjust the second offset.
(It's probably possible to make it dynamic, but my brain doesn't seem to be working for that step )

Now select cell M2, click Data Validation, select 'List' and enter this in the range/formula box: =ListForColumnM

You can now drag M2 down as far as you want and you'll get a drop-down box based on the list to the right of each drop-down box.
See the attached file which has some extra examples: Validation _ for petrosgeorgilas _ AS v2.xlsx

Hope this is more like what you need.