Sir Babydum GBE
08-10-2014, 03:54 PM
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.
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.