berkeleydave
New Member
- Joined
- Sep 2, 2011
- Messages
- 3
Hello,
I am currently working on a spreadsheet that will be used by others to enter data. The current sheet that I am working on is an attendance sheet. What I am trying to get is the following: So I have all the last and first names of the set of people that could be in a particular program. However, on the first day of the program, I could find out that a) some students did not end up enrolled in the school or b) some students do not want to participate in the program. Thus, I set up a column for these two types with data validation based on a named list to only allow the entry of "NE" for not enrolled and "NP" for not participating in this column. Now, to my question. In the remainder of the sheet, I have the various parts of the program (multiplication practice, vocab quiz, snack time, etc.) as column headings. What I would like is if "NE" or "NP" was entered in the adjacent column, all of these columns fill with "NA" BUT if the NE/NP column is blank (implying that this student if fair game for the program), then all of these cells are just regular blank cells that I can impose a data validation of values of 0 or 1 on. Just to be clear, I am not trying to accompliush (assuming I am in cell B2) =IF(OR(A2="NE",A2="NP"),"NA","") because then the cells are not truly blank, but have formulas in them that display as blank.
Can anyone help? Worst case, I can scrap the whole "fill with NAs" portion, but it would be neat to do.
Thanks!
I am currently working on a spreadsheet that will be used by others to enter data. The current sheet that I am working on is an attendance sheet. What I am trying to get is the following: So I have all the last and first names of the set of people that could be in a particular program. However, on the first day of the program, I could find out that a) some students did not end up enrolled in the school or b) some students do not want to participate in the program. Thus, I set up a column for these two types with data validation based on a named list to only allow the entry of "NE" for not enrolled and "NP" for not participating in this column. Now, to my question. In the remainder of the sheet, I have the various parts of the program (multiplication practice, vocab quiz, snack time, etc.) as column headings. What I would like is if "NE" or "NP" was entered in the adjacent column, all of these columns fill with "NA" BUT if the NE/NP column is blank (implying that this student if fair game for the program), then all of these cells are just regular blank cells that I can impose a data validation of values of 0 or 1 on. Just to be clear, I am not trying to accompliush (assuming I am in cell B2) =IF(OR(A2="NE",A2="NP"),"NA","") because then the cells are not truly blank, but have formulas in them that display as blank.
Can anyone help? Worst case, I can scrap the whole "fill with NAs" portion, but it would be neat to do.
Thanks!
