Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Data validation list moved

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Dropdown source still Lists!$A$1:$A$3 after statuses moved to column C — empty dropdown. Point validation at C1:C3 (or restore A).

About this data: Made-up entry for Harshita Nair (Trivandrum) and Jonas Weber (Vienna). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Moving helper lists without updating Data Validation breaks dropdowns quietly.

What: Lists moved; Entry validation stale.

How:

  1. Data → Data Validation on A5.
  2. See Source.
  3. Retarget to Lists!$C$1:$C$3 or restore A.
  4. Confirm dropdown items.

Ravindra Bagale’s Tip

Name the list StatusList so moves are easier to update.

Bugs explained (with mini examples)

Bug 1 — Stale validation source

Empty cells.

Bug 2 — Silent empty dropdown

Users type free text.

Practice tasks (12)

  1. Task 1. Open validation Source.
    Hint: Lists!$A$1:$A$3.
  2. Task 2. Where are statuses (broken)?
    Hint: Column C.
  3. Task 3. Fix source.
    Hint: $C$1:$C$3
  4. Task 4. Dropdown 3 items?
    Hint: Yes.
  5. Task 5. Interview.
    Hint: Validation source not updated.
  6. Task 6. Named range idea.
    Hint: StatusList.
  7. Task 7. Allow blank?
    Hint: Optional.
  8. Task 8. Input message.
    Hint: Optional.
  9. Task 9. Error alert.
    Hint: Yes.
  10. Task 10. Bonus Table list.
    Hint: Dynamic.
  11. Task 11. Document owner.
    Hint: Yes.
  12. Task 12. Save.
    Hint: Yes.
Step-by-step solution (try first)
  1. Retarget validation to the real list cells.
  2. Dropdown shows Open/Closed/Pending.

Quick quiz

Q1. Moving a list without editing validation:
Q2. Validation source lives in:
Q3. Statuses in broken file sit in:
Q4. Named ranges for lists help because:
Q5. Empty dropdown often means: