I am trying to use data validation based on multiple criteria.
Please refer to the attached sample file. In Sheet1 I have two columns: File# and Date closed. If the latter is missing, that means that the file is being processed by an agent. In Sheet2, I have the files that are on hold for whatever reason. There are four columns in this sheet: File#, Date (date it was put on hold), Deadline and Resume (date when work was resumed).
Agents only have access to Sheet2.
Now, here is the challenge: an agent can indicate the file# he wants to put on hold as long as :
- the file# is valid (number is present on Sheet1, column File#). An agent should not be able to write 1118 in the File# column, as the file doesn't exist
- the file is not closed (no date present on Sheet1, column Date Closed). For instance, the agent cannot write 1113, even if the file exists, as it is already closed (can't put on hold a closed file)
- the file# is not already on hold (which means no duplicate is allowed as long as the file is on hold). However, duplicate is allowed if the file was previously on hold but the work has resumed at a given moment in the past (an entry exists for that particular number, but a date is present in the column Resumed). For example, the agent should not be able to put on hold file 1115 as it is already on hold. However, he can put on hold file 1112, as it is still open and was on hold before (but the work has resumed since)
For the first part, I used a simple data validation (list, range, no in-cell drop down). However, since multiple criteria is involved (including lists), I admit that this is beyond my comprehension and abilities. Any help would be greatly appreciated. Thanks!
Bookmarks