Hello,
I am in the middle of building a spreadsheet for work, allowing people in the department to track the progress of students' assessments. Each student has two rows to their name. The first row would record their first attempt at the assessments, and the second row is only used if there is a need for a reassessment due to them not passing the module on the first attempt. I am using conditional formatting to shade out the second row unless it is needed. I have managed to get this to work on all of the modules for the course, but I am struggling on the last module and hoping for some help.
I am using the following formula in Conditional Formatting to check the overall grade for the module and if it is a pass then the second row will remain shaded in =or($R7=",$R7>=9).
The issue I am having with this last module is there is a second cell that also needs to be checked as it has an assessment that is simply a pass or fails. If the student fails this assessment then it would trigger a resit straight away no matter how well they did on their other assessments for that module, but I cannot work out what the formula would be to achieve this. I have attempted an OR formula with three checks included, but that keeps returning true on the $R7="" portion of the formula. I have also attempted a separate conditional formatting rule just for the Pass or Fail but again that doesn't work and I need to duplicate this a number of times so I am aware of how volatile Conditional Formatting can be.
Any help with this would be gratefully received.
I am in the middle of building a spreadsheet for work, allowing people in the department to track the progress of students' assessments. Each student has two rows to their name. The first row would record their first attempt at the assessments, and the second row is only used if there is a need for a reassessment due to them not passing the module on the first attempt. I am using conditional formatting to shade out the second row unless it is needed. I have managed to get this to work on all of the modules for the course, but I am struggling on the last module and hoping for some help.
I am using the following formula in Conditional Formatting to check the overall grade for the module and if it is a pass then the second row will remain shaded in =or($R7=",$R7>=9).
The issue I am having with this last module is there is a second cell that also needs to be checked as it has an assessment that is simply a pass or fails. If the student fails this assessment then it would trigger a resit straight away no matter how well they did on their other assessments for that module, but I cannot work out what the formula would be to achieve this. I have attempted an OR formula with three checks included, but that keeps returning true on the $R7="" portion of the formula. I have also attempted a separate conditional formatting rule just for the Pass or Fail but again that doesn't work and I need to duplicate this a number of times so I am aware of how volatile Conditional Formatting can be.
Any help with this would be gratefully received.