In reply to Glenn Sutcliffe:
congrats on the drop down box!
Re: the empty cells things it sounds like you will need a second If function, these things can go on indefinitely if you have more and more options (e.g if you want another combo to be "high risk") but I think you would need to replace the formula with this:
=IF(ISBLANK(C7),"",IF(COUNTIF(C3:C7,"No")=5,"Medium Risk","Low Risk"))
so this ^^ would be the formula in D7.
Also I assumed that you realised you need to copy the formula down the whole column as far as needed.
So now this has an added new if function which says:
If (C7), i.e. the cell to the left, isblank (i.e. is blank!) then put nothing, (i.e. ""), otherwise, if ... [the same as before (the first formula)].
Then this should just give you cells that appear blank whenever the adjacent cell is empty.
Maybe this helps you.