Alert message and special situation

[notsoXLsavy]

Could anybody tell me if this is possible?

My example situaton is:
C1:C5 is a list of people's names.

Cells in Column A are Data > Validation > List based on
=$C$1:$C$5 and drop down choices are made up to A50.

Due to a staff change, text is replaced at C2 with a new
person's name. Now the change reflects in all drop downs
from that point on!

Question:
When I click on any cell less than A51 in Column A that
contains the old C2 name, can an alert message tell me
that this person has left and will not appear in the drop
down current listing?

TIA

 

[Frank Kabel]

Hi
this would require VBA. not possible with data validation alone

 

[Biff]

Hi!

You can use the data validation feature to add an input
message whenever you select one of the affected cells.

Biff

 

[notsoXLsavy]

Thanks for this information. I've thought of an
alternative solution but need help please.
What formula can I use in 'Conditional format' so every
name in Column A that does not appear in C1:C5 changes
color?

 

[Frank Kabel]

Hi
use the following formula
=COUNTIF($C$1:$C$5,A1)=0