A drop-down source without repeats
The drop-down on the order form should offer each product once, from A to Z. In D2, build that list from B2:B8 with SORT and UNIQUE.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Product | Products | |||
| 2 | 5001 | Stapler | ||||
| 3 | 5002 | Binder | ||||
| 4 | 5003 | Stapler | ||||
| 5 | 5004 | Paper | ||||
| 6 | 5005 | Binder | ||||
| 7 | 5006 | Pens | ||||
| 8 | 5007 | Paper | ||||
| 9 |
Need help?
Why this works
UNIQUE keeps each product once, and SORT puts the short list in A to Z order: Binder, Paper, Pens, Stapler. The list spills down from D2.
In Excel, point the drop-down's Source at the spill with =$D$2#. The # means the whole spilled list, so a new product shows up in the drop-down by itself.
Common mistake: Pointing the drop-down straight at B2:B8. Every repeat then shows up in the list, once for each order.
The answer
- D2
=SORT(UNIQUE(B2:B8))
+15 points