Hey Max,

Tried your method, but all I get is a bunch of zeros in column B. Not sure
what the problem is.

Again, in the "Subjects" sheet, the students' names are listed in column A,
staring from A2, then A3, A4 and so on.

In Column B, it will list if the students are taking the subject Chemistry
or not. So in B2, it will say either "yes" or "no" to indicate if the student
listed in A2 is taking Chemistry. Similarly, in B3 it will indicate if the
student in A3 is taking Chemistry or not.

"Max" wrote:

Here's a simple formulas driven model to deliver it ...
Assume source data as described is in sheet: Subjects,
names in A2 down, subjects in B1 across, eg: Chemistry, etc

In another sheet,
Assume C1 will house the input for the subject, eg: Chemistry
In A2: =IF(C2="","",ROWS($1:1))
In B2:
=IF(OFFSET(Subjects!$A$1,ROWS($1:1),MATCH($C$1,Sub jects!$1:$1,0)-1)="Yes",ROW(),"")
In C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Subjects!A:A,SM ALL(B:B,ROWS($1:1))))
Copy A2:C2 down to cover the max expected extent of source data in Subjects.
Hide away/minimize col B. Col C will auto-return only the names for the
subject input in C1 (those marked "Yes") with all names neatly packed at the
top. Col A returns a simple auto-serializing for the names in col C. After
dressing it up as desired, just make as many copies of this sheet as required
and change the subject input in C1 to auto-extract likewise for all subjects.
You could create a DV for subjects in C1 to make it easier to select. voila?
celebrate it, hit the YES below
--
Max
Singapore
---
"prem" wrote:
Hi guys I am trying to create attendance sheets for my school students, who
are taking various subjects.

Right now, I have a sheet called "subjects" where in column A I have listed
the students' names. Column B has the header "chemistry". So students who
have enrolled for Chemistry will have the respective cell keyed in with
"yes". If they are not, "no" is entered.

Similarly, column C has the header "Physics" with either "yes" or "no" keyed
in.

What I need is this: I have another sheet named "Chemistry". So what I need
is If the student is taking Chemistry, their name should be automatically
entered in Column A of this sheet.

Similarly, I have another sheet named "Physics", where I need the names of
the students taking Physics. If the student isn't, the cell is left blank.

How might I achieve this? Thank you in advance.

Regards,
Prem Anantham