Gradebook Assistance

[Big Bill]

I have created some gradebooks for my teachers and I want them to be able to highlight a cell if a grade is missing. I only want the blank cells to format, turn yellow, when an assignment has been entered in row 1. I'm trying to create a formula that I can put into conditional formatting that basically says "If there is an assignment in B1, and cells B4:B24 are blank, change to yellow and insert text "mi" in the cell. If there is no assignment, then the cells remain blank, with no formatting." I know the solution is probably pretty simple, but I have spent way too much time on this so far. I'd like to do this without a macro if possible.

I have attached my workbook for reference.

Thanks.

 

[Ajesh]

Hi Big Bill,

See enclosed your file please.

Not sure where you want "mi" to be entered but as far as i know this cannot be done with conditional formatting.

Thanks

 

[Big Bill]

Ajesh,

Thanks for the help. I knew there would have to be a conditional format, I just didn't know how to write it. I was hoping the "mi" would appear in the colored box if no grade was entered. It stands for missing. I would love for it to say "mi" and be colored, but if they can't be combined, then just the color will work.

Thanks,
Bill

 

[Ajesh]

No worries Bill.

Also, would like to let you know that i have added an additional CB in your file for any accidental data entered in row 4 to 24 without assigning any assignment in row 1. This will turn the cell red. Try it.

Thanks.

 

[Khalid NGO]

Hi Bill,
Good day...

I think we can not format the Blank Cells in CF.
But for an alternate there is a way...

Enter this formula in CF:
=AND(B$1="Assignment",B4=0)

Format Cell as:
;;"mi"
Color the cell to your choice.

Now, instead of leaving cells blank, just enter 0 in your range.
so the CF will show "mi" only when you enter 0
--------------------

I have added another CF to revert back cell color to white when the cell is blank.

See the example:

Regards,