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.:
<tbody>
</tbody>
2) The same list of staff members, with the days of the month on which they are available, e.g.:
<tbody>
</tbody>
3) A calendar with the different areas, which I have to fill with the names of the staff:
<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
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