combination validated list and free input

[boris]

Two cells, first one "Name", the next a validated dropdown
("Dept."), based on "Name". "Dept" currently validates by
Indirect(Name cell reference). Each Name of employees is
defined as a range that has their allowable departments.
The challenge is that I want this to be able to handle non-
employees also. So I need the validation to allow for a
range name (the list of employees), but also a "Non-
Employee" entry, which would allow for someone outside of
the company to submit one of these forms.

My one idea of how to do this is to have one extra cell
below Name, which is conditionally formatted in a way that
waits for the Name cell to populate with "non-employee"
and then springs to life by turning yellow with blue text
saying "Enter name here". That way, until someone selects
from the Name dropdown that they are "non-employee", the
form seems normal. Once a "non-employee" is selected,
they have to provide the name then.

Is there a "cleaner" way of doing this, if it makes sense
at all what I am after?

 

[Guest]

Hi
I would create a userform for this to handle the validation more easily.

 

[Debra Dalgleish]

In the Data Validation dialog box for the Name cell, select the Error
Alert tab. Remove the check mark from 'Show error alert after invalid
data is entered'

In a cell in the workbook, type "N/A" (or the message you'd like to
appear when a non-employee name is entered)
Name this cell, e.g. DeptNA,

In the Dept cell, use a Data Validation formula that refers to this range:

=IF(COUNTIF(NameList,B1),INDIRECT(B1),DeptNA)

where NameList is the range that contains the employee names, and B1 is
the Name cell.