Build a dropdown from a list that isn't all still valid
Real problems with no formula named for you. You decide what to use.
Ops keeps a running list of shipping methods in D2:D5, but one entry on that list is explicitly marked retired and shouldn't make it into the dropdown. Build the dropdown from what's actually still active, then mark order #884 as Air.
The data
| A | B | D | |
|---|---|---|---|
| 1 | Order | Ship Method | Active Methods |
| 2 | #884 | Ground | |
| 3 | Air | ||
| 4 | Freight | ||
| 5 | (Retired) Overnight |
This is the actual sheet the case study hands you, including whatever column turns out not to matter. Figuring out what to ignore is part of the exercise.
Why this one matters
A source range with an entry someone forgot to delete is a common real-world trap. A dropdown is only as clean as the list you actually choose to include, not the whole range you glanced at.
More case studies
- Total vendor costs using two named ranges together
- What would the payment be on this financing deal?
- When does this project actually finish?
Case Studies are part of the full unlock
Case Studies are locked until you unlock the full site: real, messier problems with no function named for you, graded the same way every Practice exercise is. Open this one to see how to unlock the rest.
Open this case study →