Create an Excel Dropdown That Excludes Employees With Tasks in Progress
Question details
Create a task-assignment dropdown that excludes employees who have a task marked Work in Progress.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user assigns tasks from a dropdown and wants only employees without an active task to appear.
- Observed behavior
- The desired dropdown updates as task statuses change, making employees available again when they no longer have a task marked Work in Progress.
Keep a unique employee list and a task table with employee names and statuses. The example uses employees in A2:A100, assigned employees in C2:C100, and task statuses in D2:D100; adjust these ranges to fit your worksheet.
Use FILTER and COUNTIFS to build a dynamic Excel dropdown
Use this method in Microsoft 365 or another Excel version that supports dynamic arrays.
COUNTIFS checks all task rows for each employee, so an employee is excluded if even one assigned task is marked Work in Progress.
In an empty cell such as H2, enter =FILTER(A2:A100,(A2:A100<>"")*(COUNTIFS(C2:C100,A2:A100,D2:D100,"Work in Progress")=0),""). Leave the cells below H2 empty so the results can spill.
Select the task-assignment cells, then choose Data > Data Validation. Under Allow, select List.
In the Source box, enter =H2# and select OK. The dropdown will use the current results of the formula in H2.
Change an employee's task status in column D to Work in Progress, then open an assignment dropdown. Change the status again to confirm that the employee returns when no other task is in progress.

Build an available-employee dropdown in WPS Spreadsheets
Use a filtered helper list and data validation in WPS Spreadsheets to maintain an assignment dropdown. This approach requires a WPS version that supports the FILTER formula.
- 1. Arrange the worksheet: In WPS Spreadsheets, put a unique employee list in A2:A100, assigned employee names in C2:C100, and task statuses in D2:D100.
- 2. Create the helper list: In H2, enter =FILTER(A2:A100,(A2:A100<>"")*(COUNTIFS(C2:C100,A2:A100,D2:D100,"Work in Progress")=0),""). Keep H2:H100 clear of other content before entering the formula.
- 3. Set up the dropdown: Select the assignment cells and choose Data > Data Validation. Select List and use =$H$2:$H$100 as the source range.
- 4. Check the results: Change a status in column D and reopen the dropdown to check that the available names update. The fixed source range may also include blank entries.

Frequently Asked Questions
What if an employee has more than one task?
The COUNTIFS formula checks all rows in the assigned-employee and status ranges. It excludes the employee if any matching row has the status Work in Progress.
Why does the FILTER formula show a spill error?
Check the cells below H2 and clear any content blocking the results. Also make sure the formula is outside an Excel table, where spilled array formulas are not supported.
Will changing a task status remove an existing assignment?
No. Data validation controls the choices available for new entries; it does not automatically clear an employee name already entered in an assignment cell.
What happens when no employees are available?
The formula's final empty-string argument returns a blank result when no employee qualifies. Check the task statuses before choosing a name from the dropdown.




