Formula in dropdown list

[aestable]

Hello,

I am trying to create a drop-down list based on a (rather complicated) formula, and since the validation rule won't accept a formula, I'm not sure how to proceed. Essentially, I am trying to create a spreadsheet for scheduling staff in various locations, based on their qualifications and availability.

So I have three worksheets:
1) A list of staff members with the areas in which they are qualified to work, e.g.:

	Area 1	Area 2	Area 3
Archie	X	X
Barb			X
Charlie	X	X	X
Dave	X		X
Eric		X	X

<tbody>
</tbody>

2) The same list of staff members, with the days of the month on which they are available, e.g.:

	Sun 1	Mon 2	Tue 3	Wed 4	Thu 5	Fri 6
Archie	Y	Y	Y	Y	Y	N
Barb	N	N	N	N	Y	Y
Charlie	Y	N	Y	N	Y	N
Dave	N	Y	N	Y	N	Y
Eric	Y	N	Y	N	N	Y

<tbody>
</tbody>

3) A calendar with the different areas, which I have to fill with the names of the staff:

	Sun 1	Mon 2	Tue 3	Wed 4	Thu 5	Fri 6
Area 1	Archie	Archie	Charlie	etc
Area 2	Charlie		Archie
Area 3	Eric	Dave	Eric

<tbody>
</tbody>

I am at the point where I do conditional formatting which turns the background red if I enter the name of someone who is unavailable that day. But I would like to have in each cell a drop-down list with the names of the staff who are qualified to work in that area AND who are available that day. Probably a combination of INDEX MATCH and INDEX SMALL ROW, but I'm not quite at that stage yet.

I managed to pull up the list of people by qualification with an array formula that creates a list in a separate cell range, which I define by name and call up in the drop-down, but it's a bit ugly. Can anybody make a suggestion? I'm considering doing that using SQL and database tables, but I would have to develop the entire interface and I'm not quite ready for that - yet...

Thanks in advance for any advice!

Regards
Axel

 

[aestable]

I do not need help, I answered @aestable.
Maybe my answer in English is not well written?

OK, please all visitors to ignore my posts above and download links if there is a security problem

Thanks Navic - that is what I had in mind, except that as I said, with 31 days x 20 assignments I have to create 620 lists, so I was hoping to be able to use the formula directly in the list so I don't have to create 620 named ranges...