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.:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Area 1[/TD]
[TD]Area 2[/TD]
[TD]Area 3[/TD]
[/TR]
[TR]
[TD]Archie[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Barb[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]











2) The same list of staff members, with the days of the month on which they are available, e.g.:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Sun 1[/TD]
[TD]Mon 2[/TD]
[TD]Tue 3[/TD]
[TD]Wed 4[/TD]
[TD]Thu 5[/TD]
[TD]Fri 6[/TD]
[/TR]
[TR]
[TD]Archie[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Barb[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]











3) A calendar with the different areas, which I have to fill with the names of the staff:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Sun 1[/TD]
[TD]Mon 2[/TD]
[TD]Tue 3[/TD]
[TD]Wed 4[/TD]
[TD]Thu 5[/TD]
[TD]Fri 6[/TD]
[/TR]
[TR]
[TD]Area 1[/TD]
[TD]Archie[/TD]
[TD]Archie[/TD]
[TD]Charlie[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area 2[/TD]
[TD]Charlie[/TD]
[TD][/TD]
[TD]Archie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area 3[/TD]
[TD]Eric[/TD]
[TD]Dave[/TD]
[TD]Eric[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








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...