Data validation multiple criteria - Ticketing system

Occasional Contributor

Hi, everybody,


I'm trying to mimic a task system tracker using data validation and filters and I really got stuck.

So, each time I select someone to do a task from a data validation list I want that person to not be shown in the next row when I'm selecting the data validation column ( There's where the filtering function works) only if he is assigned to do a task already ( I made a second column called "Status" with a dropdown list that has 3 criteria "Completed", "Work in Progress" and "Not assigned"). 


Example: So if let's say John is assigned to do something and his Status is "Work in Progress". Well, if I have another task (next row) and I want to select someone to do it, I don't want to see John's name in the data validation list until I change John status into "Completed" or "Not Assigned"


Is there any chance to do this in excel?


Thanks a lot!

7 Replies


Where is that status column? Could you post screenshots of they layout (with anonymized data)? Or attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar, thanks a lot! I have attached a version of it now.


So let's say that from the "Engineer assign" column of the the MainTable we choose Michael from the data validation dropdown list on row number 6 and we change the status to "Work in Progress"


When I want to add another task on row number 7 and access the data validation list from "Engineer assign" column, well I expect not to see anymore Michael's name in there because it's associated with a task already (Work in progress status) and he can be selected only if we change the status of its task (row #6) to "Completed" or "Not assigned".


So there's a big algorithm that contains Filtering, Vlookup (maybe?) and data validation. 


Thanks a lot for your effort of answering me! 


Thanks. Perhaps someone else will come up with a clever data validation solution among the lines of Hide Used Items in Drop Down List 

In the attached version, I used Data Validation to highlight name conflicts.

Hi, @peiyezhu,
Well, from your video it looks great. Exactly what I was looking for.
There's a lack of experience from my side when it comes to Java Script, so I really don't know how I could get this done.
Do you have any suggestion from your side? I'm really looking forward to adopt it somehow if I can have your support on this.

Thank you for your time!

vistit this in browser,press f12 view javascript




data initate 




//select * from Activity_Planner;
$sql="select '<u>'||rowid||'</u>' rowA,* from $real_table order by rowid;";
Data_validation_multiple_criteria</b> <span>新增记录</span></div>
<form method="post" action="?p=Tools/DisTbl&g=sql"   autocomplete="off" >
<table id="oneline"><tbody><tr><td>rowid</td><td><input type="text" name="rowid" value=""></td></tr><tr><td>Date</td><td><input type="date" name="Date" value="2023-03-21"></td></tr>
$sqlDatalist="select a.Engineer_assign from Engineer_assign a left join $real_table b using(Engineer_assign) where b.status is null or b.Status='Completed'";
<tr><td>Task_description</td><td><input type="text" name="Task_description" value="aa" required="">
$sqlDatalist="select Status from Status_of_Work";
<tr><td>Other_observation</td><td><input type="text" name="Other_observation" value="bb" required="">
</tbody></table><input type="submit" id="submitForm" value="保存" style="height:2em">
const table = document.querySelector(".main_tbl");
for (let i = 0; i < table.rows.length; i++) {
  const cell = table.rows[i].cells[0];
const row=table.rows[i]
  cell.addEventListener("click", function() {
	let inputs=document.querySelectorAll('form input')
	for(let col=0;col<(inputs.length-1);col++){



excel template for data initiate