Forum Discussion

gillgill's avatar
gillgill
Copper Contributor
Jul 17, 2025

Preventing duplicate selections

Hi all!

What I'm trying to do: 

Once a company is selected on one employee's sheet, it's removed from the dropdown lists on all the others, preventing duplicate selections. 

Background:

I'm builidng a workbook with 18 sheets- one per employee. Each sheet has the same structure: column A contains a dropdown list of client companies, pulled from a master list on a separate sheet (Company List). When an employee selects a company, related contact info (name, phone, email) auto-fills using XLOOKUP. 

My setup so far: 

- The Company List sheet stores the master list of companies and contact info. 

- Dropdowns in each employee sheet (column A) are already pulling from that list.

- Selection triggers auto-fill of contact details (working fine) 

admin
excel
Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 19, 2025

    Let assume you have Company List sheet as

    and in each employee sheet (in the sample from A to C) data starts exactly from A5

    With that we may generate spill for validation which has values from Company List not repeating in employee sheets

    =UNIQUE( VSTACK( TOCOL( $B$5:$B$70, 3), TOCOL( VSTACK('A:C'!$A$5:$A$70),3 ) ),,1)

    and use this spill in data validation as

    Please check in attached file

Resources