Creating a text cell which responds to values on a data validation list

[Old Geezer]

Help please, this is beyond my modest excel skills.

I would like to create a cell which “shouts out” if a target has been achieved on a option.

A1 contains a data validation list. A B C D
A2 contains my manually entered value, say (450).
A3 I would like to automatically state ‘On Target’ with a green background fill or ‘Under target’ with a red background fill.

The targets are
A 500
B 500
C 250
D 200

Many thanks for any assistance.

[CAntosh]

In A3, try the following, then fill right as needed:

=IF(AND(A$1="A",A$2>=500),"On Target",IF(AND(A$1="B",A$2>=500),"On Target",IF(AND(A$1="C",A$2>=250),"On Target",IF(AND(A$1="D",A$2>=200),"On Target","Under Target"))))

You can get your coloring with conditional formatting:

Select A3 to the end of your data and use the following rule for green:

=A3="On Target"

Same for red, but use:

=A3="Under Target"

[FoxSeaLady]

See that attached file. I think this will answer you problems in conditional formatting and the appropriate value in cell A3.

Let me know how you go.

[Old Geezer]

Thank you both for you assistance.

I used FoxSeaLady's file, this has worked a treat. As well as solving my issue, both replies have expanded my understanding of excel.

Once again, many thanks for your time.