I am trying to create a fairly complex scheduling worksheet. In my
schedule, I havea data validation on employees names t hat pulls from
an offset list containing assigned soutes that they can service. This
works fine...I have used a formula =IF($IR9=0, NameCheck,
OFFSET(RouteStart, MATCH($C9, RouteCol, 0)-1, -3, COUNTIF(RouteCol,
$C9),1))

Where NameCheck is the full list of available employees (employees are
taken out of this when added to the range we are working in).

Where RouteStart/Routecol define the routes of work that employees on
the namecheck list are assigned to

where IR9=0, shows the complete list of available employees when no
route is selected.


I want to refine my sheet in two ways:

1) I have superrvisors in column A that are assigned to employees.
Can I make an option where the data validation is dependent on employee
as an override if no route employees are available? I want to keep
both these complex validations and have a choice to which shows up

2) Is there a way to lay a formula in a validated cell and then, have
it pasted back in the cell if the cell is deleted or reset in any way?