Function Problems

Create an Excel Dropdown That Excludes Employees With Tasks in Progress

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 868 views

Question details

Create a task-assignment dropdown that excludes employees who have a task marked Work in Progress.

Create an Excel Dropdown That Excludes Employees With Tasks 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.
Before you start

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.

Solution 1Recommended

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.

1
Create the available employee list

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.

2
Open data validation

Select the task-assignment cells, then choose Data > Data Validation. Under Allow, select List.

3
Use the spilled list

In the Source box, enter =H2# and select OK. The dropdown will use the current results of the formula in H2.

4
Test a status change

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.

Use FILTER and COUNTIFS to build a dynamic Excel dropdown
Existing assignments remain: Changing the dropdown list does not automatically remove names already entered in assignment cells. If no employees are available, the formula returns an empty result.
WPS Spreadsheets

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. 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. 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. 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. 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.
Exclude employees with tasks marked Work in ProgressOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceUpdate the helper list as task statuses change
microsoft office alternative - wps office

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.