Data validation - more questions

[Damo71]

Hi again

I'm still exploring data validation options. And need to know if something is possible.

I have a list of student names in sheet 1, column A. The range is named "Students"

There are various assignment types listed in row 1. For example, B1 contains "Discourse", C1 "Demonstration" & D1 "Soliloquy". Each of those columns are named ranges also (same names as the header)

Then, under each of those headings I type "yes" next to the student's name if that student can be given that particular assignment.

On sheet 2 I have a calendar In which I will assign a different student each week. But only the student names that are qualified should appear in the dropdown lists for any given assignment. Can this be done with a formula in data validation? or will I need helper columns to formulaically reduce-down the lists of names for each assignment?

Thanks

(I wanted to attach a workbook, but don't know how)

 

[mumps]

You can't attach files in this forum. If you want to attach a file, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. You can post screen shots. Click this link and look at Section B: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html