Drop-down lists and data validation practice exercises
Write the formulas behind drop-down lists and data validation rules: a source list without repeats, a list that depends on another choice, weekdays only, no duplicates and codes in capitals.
- 1A drop-down source without repeatsThe 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.
- 2A list that depends on the first choicePremiumThe second drop-down should only offer items in the category picked in E1. In E2, list the items in B2:B8 whose category in A2:A8 matches E1, with FILTER.
- 3Weekdays onlyPremiumDeliveries only go out Monday to Friday. Write the custom validation rule for B2 in C2. With 2 as the second argument, WEEKDAY counts Monday as 1, so a weekday is less than 6.
- 4Block duplicatesPremiumEach customer ID may only be used once. The rule is set on A2:A6 and written for A2. In C2, count how many times A2 appears in A2:A6 with COUNTIF and test that it is exactly 1. Lock the list with $.
- 5Codes in capitals onlyPremiumProduct codes must be typed in capital letters. Write the rule for A2 in C2. Compare A2 with UPPER(A2) using EXACT, since a plain = ignores case.