Conditional formatting overwriting conditional formatting?

Copper Contributor

Hi! I'm trying to use conditional formatting to display text alerts depending on user input. I have created a simplified example. In the given example, on sheet 2, the user will input their score (here '2', '1', or '0') in response to a given criteria (here, Criteria 1 and Criteria 2). The Alert column (E) will then display the appropriate alert based on the input. Alerts are derived from sheet 1.

 

The difficulty I'm having is that I need the alerts for criteria 1 and criteria 2 to appear in the same cells (E2&E3), but only appear when the use input is entered. That is, if the use enters '2' for criteria 1, the criteria 1 alerts need to show. When they subsequently enter a value for criteria 2, the criteria 2 alterts need to appear in place of the criteria 1 alerts. At present, once the criteria 1 alerts show, they remain (they're not replaced by criteria 2).

 

Example here: https://docs.google.com/spreadsheets/d/1zK8Y9x2Fc_SGh4BeCog667vnGZZIDs1k/edit?usp=sharing&ouid=10629...

 

I would be incredibly grateful for any suggestions as I'm not even sure this is possible? 

5 Replies
well, when I first went to the sheet it looked like you were there so I started typing some comments there but I guess I just missed you. In any case I left comments and an idea for a formula and set up that might work for you. Note that it is in that google sheet and not excel and therefore things will change slightly if you are copying it back in to excel.
Hey! Thanks so so much for your reply. I really appreciate your time.
I've gone through and tried to elaborate. Your solution so far is super - really so much closer than what I'd come up with. If you have any time, I'd be incredibly grateful for any further input, but I realise it might be a bit of a pain in the neck..
Thanks again!
sorry, I don't see any elaboration.
Sorry I've no idea why it didn't save. Possibly internet connection issues my end. Thank you again for your time - hopefully my comments are visible now!
I still have some confusion but created a formula that might be closer to what you want.