Task 1 of 4
Name the model lists: "Laptops" is E2:E4, "Phones" is F2:F4, "Headphones" is G2:G3.
Lesson theory
A dependent list changes depending on a neighboring cell: pick the category "Phones" and the second list shows only phones.
- Give each list of models a name equal to the category: select E2:E4 and type
Laptopsin the Name Box to the left of the formula bar (in Excel you can also use Formulas › Define Name). A name cannot contain spaces; - in the category column, make an ordinary list: Data › Data Validation › List, source
$E$1:$G$1; - in the model column, make a list with the source
=INDIRECT(A2). INDIRECT turns the text "Phones" into a reference to the range with that name. Leave A2 without a $: in B3 it becomes A3, in B4 it becomes A4.
If the category in column A is empty, INDIRECT returns an error and the list is empty. That is exactly what should happen.
Practice is part of Pro
The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.