Stuck on Formatting

[seehornh]

Ok. I might be in way over my head with this one, but I am going to give it a go anyways. I have created a scheduling spreadsheet on Excel that takes student class schedules and then displays their work schedules. It's all based on conditional formatting. Basically, I type in when the student's have class, so for example, 8:00-12:00, and then spreadhseet I created uses the conditional formatting to black out the time segments the students cannot work due to classes and makes the cells they can work turn a specified color. There is room for 8 students. Now, what I am trying to do is figure out a way to create a drop-down list in another table based on who is availiable. For example, student 1 and student 3 are the only students that are able to work the 8:30 shift. So what I want to happen is when I go into the second table, there is a dropdown that comes down for the 8:30 shift that only gives me an option to click either student 1 or student 3. Any helpful ideas? I can provide more details if what I am trying to accomplish is not clear.

[seehornh]

I guess a simpler way of explaining what I am trying to do: create a drop down list based on cells that are conditionally formatted in a specific way in another table.

[MarvinP]

Hi seehornh,

Dropdown lists are normally based on data, not conditional formatting. I'd try to create the list based on a table of data and then perhaps use that table to do the CF??

[63falcondude]

The way that I would go about this is to build a formula or named range that lists all of the names in cells, then figure out how to apply that formula in a data validation list for your dropdown.

If you would like more specific assistance, please see the yellow banner at the top of the page and upload a small representative sample along with the desired results based on that sample.

Also, it would be helpful to know what version of Excel you are using. 2010, 2013, 2016, 365, ...

[seehornh]

Ok I have attached the spreadsheet. What I am trying to do is get drop-down lists to show up for the cells in the bottom table based on who is availiable to work.

[63falcondude]

Nothing has been attached. Also, you still have not updated your profile to tell us what version of office you are using.

[seehornh]

I am using Microsoft Office 2019 MSO. I guess that is what you are asking. I am going to try for the fourth time to upload the sample workbook

[seehornh]

Sample Scheduler.xlsx
Hopefully this works as the attachment

[63falcondude]

Are the desired results (based on the sample from post #8) manually mocked up somewhere in there?

Sorry if I missed it, there's a lot going on there and what you are looking for (based on that sample) isn't clear to me.

[seehornh]

I do not have the desired results mocked up, but could probably do so if what I want doesnt make sense.

What I am trying to do is this: There are three main sections of the excel sheet. One, on the left hand side, is a conditionally formatted schedule for 8 students based on their availiablity, which is determined in the second large area on the right, where you can put student's classtimes in, and it will black out those times in the area on the left. Now, I am trying to create a another third section. Ideally, this section sees which students are availiable and only list those students in a dropdown list. For example, student 1 and student 2 have class from 8:00-9:00, while student three has a class from 8:30-9:00. Thus, student 3 is the only student that can work the 8:00 AM block. Based on this, I want to make the excl sheet in a way that when I go to select a student in this third area to work at 8:00, a dropdown list will apear with only student 3's name. Then, if all three students can work the 11:00 block, all three student names will appear in the dropdown list.

[JeteMc]

This proposal employs a couple of helper ranges:
In the first range (AH1:BD64):
1. AI2:AI64 is populated using: =IF(Z3<>"",Z3,AI2)
2. AJ2:BD64 is populated using:

=IF(NOT(ISNUMBER($AA2)),"",IF(NOT(OR(AND(AJ$1>=$AA2,AJ$1<=$AB2),AND(AJ$1>=$AC2,AJ$1<=$AD2),AND(AJ$1>=$AE2,AJ$1<=$AF2))),$AI2,""))

In the second range (AH66:BD105), AJ66:BD105 is populated using:

=IFERROR(INDEX(AJ$3:AJ$63,AGGREGATE(15,6,(ROW(AJ$3:AJ$63)-ROW(AJ$2))/(AJ$3:AJ$63<>"")/($AH$3:$AH$63=$AH66),COUNTIFS($AH$66:$AH66,$AH66))),"")

The Monday drop downs (B62:U64) have the source:

=OFFSET($AJ$66,MATCH($A$60,$AH$66:$AH$105,0)-1,MATCH(B$61,$AJ$1:$BD$1,0)-1,SUMPRODUCT(($AJ$66:$BD$105<>"")*($AH$66:$AH$105=$A$60)*($AJ$1:$BD$1=B$61)),1)

Let us know if you have any questions.