Help creating attendance sheet.

[prem]

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

 

[Bernard Liengme]

It would be much easier to do it the other way around.
On the Chemistry sheet enter rerolled student names in starting in A2
Ditto on Physics, etc
On Main sheet Copy and paste all the names from the other sheets; sort and
use Data Filter to get rid of duplicates
Let's say the first name is in A2
In B2 enter =IF(COUNTIF(Chemistry!A:A,"dog"),"yes","no")
Copy down the column
In C2 =IF(COUNTIF(Physics!A:A,"dog"),"yes","no")
best wishes

 

[Max]

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,Subjects!$1:$1,0)-1)="Yes",ROW(),"")
In C2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(Subjects!A:A,SMALL(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

 

[prem]

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]

Prem: Should have worked exactly as I advertised. Re-check carefully the 3
top line formulas provided that you entered into A2:C2. Ensure that these
correspond exactly with what I responsed, especially this term: ROWS($1:1).
Excel has a nasty habit of tending to remove the "$" sign in ROWS($1:1) if
you rush through its prompts when you enter the formulas. Try it again, post
back here. Zonk the YES below to celebrate success.

 

[prem]

Hi Max,

I copied and pasted your formulae into cells A2, B2 and C2 of my new sheet.
I then copied the respective formulae down columns A, B and C.

Columns A and C are blank. In column B, it returns "#N/A".

Not sure if this could be a problem but in my "Subjects" sheet, I select the
"Yes" or "No" options using a dropdown list.

Thank you for your patience.

 

[Max]

DVs should not pose any problems. But you could check that the inputs: Yes,
No do not contain any extra white spaces which might be throwing correct
matches off.
Take away this working sample to see where the actual implementation issue
lies with you over there:
http://cjoint.com/?byiGLVALwt
Let me know here

 

[prem]

Hi Max,

I just realized what the problem was. I misunderstood what you meant by
"Assume C1 will house the input for the subject, eg: Chemistry:".

I thought this was for my original subject list, not the new sheet fir the
attendance. Thank you for the help. It worked. Much appreciated

Regards,
Prem Ananthan.

 

[Max]

Ahhh, welcome. Glad that you got it up finally.