Dynamic Validation List based on Criteria

I'm newly registered to this forum, so forgive any ignorance on my part here. You guys have been fantastic so many times in the past with some sticky problems I've had, so I'm hoping you may be able to help me with this one.

I have the following worksheets, for example. In reality I have about 150 workers, and a maximum of 50 locations. Some workers will only ever cover one location, some a dozen, say, and some all 50 locations. I considered creating separate lists for each location, with worker's name in them, and using a "switch list" technique, but this seemed far too inelegant, so I thought I post this thread before I proceed.

Worksheet 1

COL A COL B
LOCATION Allocate To
London Validation List Here
London Validation List Here
New York Validation List Here
Paris Validation List Here
Stockholm Validation List Here

Worksheet 2

COL A COL B COL C COL D
WORKER LOCATION1 LOCATION2 LOCATION3
Mike London New York Paris
Nancy London Stockholm
Alice Paris New York
Greg London

In the first worksheet in Column B, I'd like a dynamic validation list that will only show the names of the workers that cover the location that is shown each cell of Col A. The locations the workers cover may change, but rarely. The problem is, the data in Worksheet 1 will be copied in each week, although the variety of locations will be the same. I know I'll have to update Col B each week, but if I can minimize the effort, brilliant!

If I can avoid any VB code that would be ideal.

I really hope your collective genius can assist.

Please post a sample file

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Check this file.

Does it do the job?