I am creating a table to allow a user to add employees to a project and I wish to protect it from user error as best I can.

The table looks like the following

The user adds an employee to a project and then enters the dates they wish them to be on it. I'd like to catch if the date entered in to the Start Date is within the project start date and end date (On the Project Info sheet) and show a warning message if not. I'd also like to have an additional check to determine if the start date is within the employee start date (on the Staff Details sheet) and employee end date (this could be blank) and show a different message if it isn't.

Project Info Sheet Layout :

Staff Details Sheet Layout :

Is it possible to trigger a warning message straight after the user has input some data into a specifc column cell and if so, how would I go about it?

CodePudding user response：

Assuming your Project Info sheet is a table I've named ProjectTable and your main table is on Sheet1.

With cell F2 selected create this Name I've called ProjectStartDate - (Formulas > Name Manager) =XLOOKUP(Sheet1!$B2,ProjectTable[Project Name],ProjectTable[Start Date])
This will return the Start Date for the specified project.
Sheet1!$B2 should reference the Project Name.

In Data Validation add this Custom validation:
=$F2>=ProjectStartDate

Now do the same for the end date -
=XLOOKUP(Sheet1!$B2,ProjectTable[Project Name],ProjectTable[End Date]) and
$G2<=ProjectEndDate