Build a Dropdown List That Removes Items Once They're Picked

Here’s a sentence that should never be true, and yet: “Wait, didn’t we already give Laptop-04 to someone else?”

Two people picked the same item from the same dropdown, on the same sheet, and Excel didn’t say a word — because a standard Data Validation list doesn’t know or care what’s already been chosen in the cells around it. It just shows the same static list, forever, to everyone.

Fixing that isn’t hard once you know the trick, but almost nobody stumbles onto it by accident, because there’s no setting for it. Grab a coffee: we’ll do the classic version that works in any Excel since 2007, then the 365 one-liner that replaces all three helper columns.

Who this quietly bites

This shows up anywhere a fixed pool of things gets assigned one-to-one through a dropdown column — a list of resources on one side, a list of requests on the other, and a single Excel column connecting them:

• Loaner equipment sign-out — laptops, projectors, badges, company phones. Two employees both “have” Laptop-04 on paper.
• Meeting room or desk booking sheets — the same room gets double-booked for the same week.
• Shift or on-call rosters — two people accidentally both get assigned Saturday’s on-call slot, or nobody does.
• Event and raffle logistics — table numbers, sponsor booths, prize items assigned more than once.
• Fleet and vehicle checkout logs — the same van “booked” by two different drivers.

The common shape: a finite list of things, and a booking column where each pick should come out of the pool for everyone else. Plain Data Validation has no concept of “already used” — it shows the same full list in every cell, so the only thing stopping a double-pick is someone reading carefully. That doesn’t scale past about five rows.

Why Data Validation gets this wrong

It’s worth saying plainly: there’s no checkbox for this. Data → Data Validation → List just points at a range and shows whatever’s in it. It has no built-in “hide what’s already chosen” mode, in any version of Excel, including 365.

To get that behaviour, the list source itself has to be a formula that recalculates down to “only what’s left,” and the dropdown has to point at that formula’s output instead of the static master list. That’s the whole trick, and how you build it depends on which Excel you’re on.

The fix, two ways

The scenario, in full. Ten laptops on a Lists sheet in A4:A13. Twelve employees on a Sign-Out Log sheet in rows 4 to 15, each picking one. Twelve rows, ten laptops — so both techniques get to run out, which is the case most write-ups skip.

The goal is a third list — “available” — that is always the master list minus whatever is already sitting in the booking column, and that is what Data Validation points at.

Option 1 — Classic (backward-compatible functions, step by step)

This is the well-known dropdown-without-duplicates technique. Three small helper formulas and one defined name — no array entry, no Ctrl+Shift+Enter, nothing version-specific.

Step 1 — Rank the items that are still available.

Next to your master list, a running-count formula that only numbers items not yet in the booking column:

Lists!B4  =IF(COUNTIF('Sign-Out Log'!$B$4:$B$15,A4)=0, MAX($B$3:B3)+1, "")

Fill down. Items already booked get ""; items still free get the next number in sequence — 1, 2, 3 — regardless of where they sit in the master list. $B$3 is the header row, which MAX ignores because it’s text, so the running maximum starts at zero on its own.

Step 2 — Pull those ranked items into a clean, gap-free list.

Lists!C4  =IFERROR(INDEX($A$4:$A$13, MATCH(ROWS($C$4:C4), $B$4:$B$13, 0)), "")

Fill down. MATCH(ROWS(...)) asks for rank 1, then rank 2, and INDEX returns whichever laptop holds it — compacting the scattered “available” items from column B into a tidy list with no blanks in the middle. IFERROR turns “no rank that high” into a blank rather than #N/A.

Step 3 — Name that list, sized to fit exactly what’s left.

Formulas → Define Name → AvailableResources, referring to:

=OFFSET(Lists!$C$4, 0, 0, MAX(COUNT(Lists!$B$4:$B$13), 1), 1)

Step 4 — Point the dropdown at the name.

Select 'Sign-Out Log'!B4:B15 → Data → Data Validation → List → Source: =AvailableResources. Done. Pick a laptop in row 4 and it vanishes from the dropdown in every other row, instantly, no macro required.

Option 2 — Excel 365 spill (dynamic-array equivalent)

On 365 you can skip the rank-then-compact dance entirely. FILTER does both jobs in one spilled formula:

Lists!D4  =FILTER($A$4:$A$13, COUNTIF('Sign-Out Log'!$C$4:$C$15,$A$4:$A$13)=0, "All assigned")

Read it left to right: filter the master list down to only the items whose COUNTIF count in the booking column is zero — that is, not yet picked.

The third argument is the part classic Excel cannot do at all. If literally nothing is left, the formula spills the text “All assigned” instead of throwing #CALC!. No padding trick needed; 365 handles the empty case natively, which is exactly the case MAX(...,1) had to be bolted on for.

Now name the spill, not a fixed range:

=Lists!$D$4#

That trailing # is the spill reference operator — it means “however many rows this formula happens to produce, right now.” Point Data Validation’s Source at =AvailableResources365 and the dropdown tracks the FILTER output exactly, growing and shrinking as people pick and unpick.

Letting someone change their mind

Both versions share one quirk. The “available” list is shared across the whole booking column, including the cell being edited. So if B7 already has Laptop-07 and you reopen that cell’s dropdown to switch it, Laptop-07 won’t be in the list — as far as the formula is concerned it’s still taken, by that very cell.

The fix is simple and matches how these sheets actually get used: clear the cell first (select it, press Delete), then reopen the dropdown. The freed-up item reappears everywhere immediately, since every dropdown is reading the same live list.

The hole neither version closes

Data Validation blocks typing and blocks picking. It does not block pasting.

Paste a value over a validated cell and Excel accepts it silently — and worse, on some builds the paste carries the source cell’s validation with it, so the rule is gone as well as broken. On a sheet other people fill in, that is not a theoretical risk; it is how these sheets are actually filled in.

The answer is a visible check rather than a stricter rule:

=SUMPRODUCT(--(COUNTIF('Sign-Out Log'!$B$4:$B$15, Lists!$A$4:$A$13)>1))

Zero means nothing is double-booked. Anything else names the problem before someone drives to the office to collect a laptop that isn’t there. The dropdown is what prevents the double-pick; this is what proves there wasn’t one.

Where else this pattern pays for itself

• Interview and appointment slot sheets — each time slot assigned to exactly one candidate.
• Team task boards — a fixed set of task IDs handed out one owner each.
• Volunteer sign-ups — roles or shifts that shouldn’t be filled twice.
• Licence and seat allocation — a fixed number of software seats assigned to named users.
• Wedding and event seating — table numbers assigned to guests without a clash.

Anywhere the sentence “there’s a limited pool, and every pick should come out of it for everyone else” applies, this pattern fits.

Download the workbook

A laptop loaner sign-out sheet with 12 employee rows but only 10 laptops — so you can watch both techniques run out gracefully and show “All assigned.”

Four sheets: Lists holds the master pool and every helper formula, with the classic rank-and-compact columns and the single FILTER side by side, plus both defined names written out so you can see exactly what each resolves to. Sign-Out Log has the two dropdown columns, three rows pre-filled so the effect is visible the moment you open the file — open the dropdown on row 7 and three laptops are already gone from it. Check counts the pool, the bookings and what’s left, proves the two techniques agree, and carries the double-booking test above for both columns.

The two dropdown columns watch separate booking columns on purpose, so you can compare the techniques in one file. In a real sheet you’d keep one booking column and one technique.

Download the no-duplicate-dropdown workbook (.xlsx)

The FILTER column needs Microsoft 365 or Excel 2021+. The classic columns open in anything back to 2007.

Takeaways

• Data Validation → List always shows the same static range to everyone. It has no idea what’s already been picked elsewhere, in any Excel version, and there is no setting that changes that.
• To make picks disappear from the pool, the list source has to be a formula, and Data Validation has to point at that formula’s output.
• Classic (Excel 2007+): a running-rank IF(COUNTIF(...)=0, MAX(...)+1, "") helper, compacted with INDEX/MATCH, wrapped in an OFFSET-based defined name. MAX(..., 1) is what stops it breaking when the pool empties.
• Excel 365: one line — =FILTER(list, COUNTIF(booking_range, list)=0, "All assigned") — named via a spill reference (Lists!$D$4#) instead of OFFSET. The third argument is the bit classic Excel can’t do.
• To change a pick, clear the cell first, then reselect. The freed item reappears in every dropdown immediately.
• Data Validation does not block pasting. Keep a visible SUMPRODUCT(--(COUNTIF(...)>1)) duplicate check on any sheet other people fill in — the dropdown prevents, the check proves.
• Your move: open the booking sheet you rely on most and run that duplicate check over it once. It takes thirty seconds, and it either reassures you or tells you something you needed to know.