Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "prem" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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. -- Max Singapore --- "prem" wrote: 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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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" wrote: 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. -- Max Singapore --- "prem" wrote: 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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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 the http://cjoint.com/?byiGLVALwt Let me know here -- Max Singapore --- "prem" wrote: 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. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
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" wrote: 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 the http://cjoint.com/?byiGLVALwt Let me know here -- Max Singapore --- "prem" wrote: 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. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help creating attendance sheet.
Ahhh, welcome. Glad that you got it up finally.
-- Max Singapore "prem" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attendance Sheet. | Excel Discussion (Misc queries) | |||
Need help creating an attendance worksheet | Excel Discussion (Misc queries) | |||
Help with Volunteer Attendance Sheet | Excel Discussion (Misc queries) | |||
attendance sheet | Excel Worksheet Functions | |||
attendance sheet to add time but not..... | Excel Worksheet Functions |