Create sub lists from main list

[Sir Babydum GBE]

Firstly, it's nice to be back! Those of you who've been here a few years will remember me!

anyhow. About 5 years ago I built a spreadsheet that helped schedule people into a school on a rotational basis. That is... Of about 150 students, each would be given an assignment about once every 6 weeks. I used a combination of formulae, vba, pivot tables etc so that the scheduler would see a list of names in a drop down list where the person who had most recently given an assignment would now appear on the bottom of the list, and the person who had an assignment six weeks ago would appear on or near the top.

I have now been given the duty to revamp the spreadsheet for use on the ipad. Excel for iPaf doesn't appear to support vba and dynamic updating of pivot tables, so I need a purely formulaic approach so that the system will work fully on the ipad.

so my first question is (sorry about the lengthy preamble):

how do I create a sublist from a large list?

On sheet 1 for example, suppose I have a list of 150 names in column A. The headers on row 1 are: Student, Maths, English, Geography, chemistry, Biology, Physics.

so on sheet 2 I want formulas to return a gapless list of students who can take on Geography assignments (there'd be a "Yes" next to their name in the geography column.

then another list of people who did the sciences (they should appear on the list if they did ANY of the 3 science subjects.

can this be done with formulas? Because then I'd use the list returned to form the basis of a dropdown validation.

there may be more questions...

thx in advance.

[Sir Babydum GBE]

Ok, i talked way too much last time, so here is an example. In the attached, I need formulae to return lists of names as appropriate in the spaces provided in the second tab, based on the info provided in the first tab. the lists should have no spaces between students. This is why i can't work it out myself.

[Bob Phillips]

This will give you male Maths students,

=IFERROR(INDEX(Students!$A$2:$A$21,SMALL(IF((Students!$C$2:$C$21="Yes")*(St udents!$B$2:$B$21="M"),ROW(Students!$B$2:$B$21)-ROW($B$2)+1),ROW(A1))),"")

I will leave the rest to you.

[Sir Babydum GBE]

Thanks XLD!

when I get home I'll try this out. I hope I can work out the rest.

[Bob Phillips]

The * operator is acting as an AND, Maths AND Male for example. When you have any of the three sciences, you need + as an OR operator (rng1="Yes")+(rng2="Yes")..., but remember you still need to AND Male/Female, so the usual rules about ANDs and ORs apply.

[Sir Babydum GBE]

Entered as an array...

works like a charm. Thank you very much!