How to Prevent Duplicate Drop-Down Selections Across Excel Sheets
Question details
The user wants to create a dynamic drop-down list that automatically removes options that have already been selected on other worksheets to avoid duplicate assignments.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Assigning clients or companies to employees across different worksheets without assigning the same client twice.
- Observed behavior
- Currently, the drop-down list shows all options, making it possible to accidentally select a company that is already assigned to someone else on another sheet.
Ensure that your master list of companies is up to date and identify the exact range of cells where selections will be made on each employee's worksheet. Note that this solution requires a spreadsheet software version that supports modern dynamic array functions like UNIQUE and VSTACK.
Create a Dynamic Spilled Array for Data Validation
Use dynamic array functions to combine selected data across sheets and filter out duplicates from your master list, creating a source for your drop-down list.
By stacking the master list with the lists of already selected items and finding the unique values that only appear once, you can generate a dynamic list of available companies. This 'spill range' is then used as the source for your data validation drop-down.
Note the location of your master company list (e.g., $B$5:$B$70 on your Company List sheet) and the ranges where selections are made on the employee sheets (e.g., a 3D reference like 'A:C'!$A$5:$A$70).
In an empty column that will serve as your helper list, enter the formula: =UNIQUE(VSTACK(TOCOL($B$5:$B$70,3),TOCOL(VSTACK('A:C'!$A$5:$A$70),3)),,1). This formula ignores blanks, stacks the lists, and extracts values that appear exactly once.
Select the cells on your employee sheets where you want the drop-down list. Go to the Data tab and click Data Validation. Choose 'List' from the Allow menu. In the Source box, reference the first cell of your spill range followed by a hash symbol (e.g., =$Z$1#), then click OK.
Create Dynamic Drop-Downs Easily with WPS Office
WPS Spreadsheet fully supports advanced array functions, allowing you to seamlessly manage dynamic drop-down lists and avoid duplicate data entry across multiple sheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the employee worksheets and master list.
- 2. Apply the array formula: Type your dynamic array formula combining UNIQUE, TOCOL, and VSTACK into an empty helper column to automatically generate your available options.
- 3. Set Data Validation: Navigate to the Data tab, click 'Validation', select 'List', and reference your dynamic spill range using the hash symbol (e.g., =$Z$1#) to enforce unique selections.
Frequently Asked Questions
Why isn't my spill range working in the Data Validation source box?
Ensure you add the hash symbol (#) at the end of the cell reference (e.g., =$Z$1#) in the Data Validation Source box. This tells the software to reference the entire dynamic spilled array rather than just the single cell.
What does the '3' mean in the TOCOL function?
The second argument in the TOCOL function controls what types of cells to ignore. A value of 3 tells the function to ignore both blanks and errors when converting the array to a single column, keeping your data clean.
How do I reference multiple sheets in the VSTACK function?
You can use a 3D reference by specifying the first and last sheet names separated by a colon, followed by an exclamation mark and the cell range. For example, 'Sheet1:Sheet3'!A1:A10 will stack data from the exact range across Sheet1, Sheet2, and Sheet3.
Can I prevent duplicate drop-down selections without dynamic arrays?
Yes, but it is much more complex. In older versions of Excel that do not support dynamic arrays, you will need to set up multiple helper columns using COUNTIF and INDEX/MATCH functions to filter out used items, or write a custom VBA macro.
