This is a classic spreadsheet headache, and the frustration in the request is entirely justified. You have a list of employees, a set of work centers, and a simple rule: one person, one assignment. The manual solution, building a separate, filtered dropdown for every single cell, is brittle and time-consuming. It works for a small team on a Tuesday, but it breaks the moment someone calls in sick or a new hire starts. The real issue isn't the dropdown. It's that the spreadsheet lacks any concept of a constraint. It sees a list of names, but it doesn't understand that assigning a person to one cell should logically remove them from the options in every other cell.
What this user is really asking for is a system that enforces a rule, not just one that displays data. Traditional formulas can fake this behavior. You can write a nested `INDIRECT` and `FILTER` combination that checks a range, identifies used names, and rebuilds the list for each new dropdown. It works, but it is fragile. A stray sort, a moved column, or a copy-paste error will corrupt the logic, and debugging that formula chain requires the patience of a spreadsheet detective. The tool is asking you to manage the logic instead of managing the outcome.
A smarter approach makes the rule part of the tool itself. An AI-native spreadsheet can understand the relationship between a dropdown selection and the rest of the list. When you assign an employee to Work Center A, the system recognizes that name as consumed. Every subsequent dropdown in that row or column automatically removes that person from its options. No formulas to write, no ranges to update, no cascading failures when a cell shifts. The intelligence is in the structure, not in the cell. This turns a fragile workaround into a reliable process.
The lesson here is practical. If you find yourself building a complex formula just to enforce a simple rule like "assign each person once," you have hit the ceiling of what traditional spreadsheets do well. You are spending your energy on the plumbing instead of the work itself. The solution is not a better formula. It is a tool that treats your data as a connected system, where changing one value automatically updates every related choice. Explore tools that understand constraints, not just cells. That is how you stop wrestling with dropdowns and start managing your team.