You can make data entry easier in Excel, by create a drop down list with data validation. Sometimes those lists are so long, that they become a pain to use. Here’s a technique from AlexJ, that lets users switch between a full list of customers, and a short list of top customers.
The technique is driven by a formula — no macros required!
Set Up the Lists
The first step is to create two named lists — they will be used as the source for the drop down lists.
- Type the full list of customers
- Select all the cells in that list, and name the range: FullList
- In another column, type “Full List” in row 2.
- Starting in row 3, type the short list of top customers
- Select all the cells in that list, including the “Full List” cell
- Name that range: ShortList
Create the Drop Down List
Next, you’ll create the drop down list, by using data validation with an IF formula:
- Select the cell(s) where you want the drop down list of customers
- On the Ribbon, click the Data tab, then click Data Validation
- In the Data Validation dialog box, under Allow, select List
- In the Source box, type an IF formula that refers to the active cell, and the named lists. If you’re using the list in a single cell, you can use an absolute reference, e.g. $E$3, and for multiple cells, use a relative reference, e.g. E3
- =IF($E$3=”Full List”,FullList,ShortList)
- =IF(E3=”Full List”,FullList,ShortList)
- Click OK, to close the dialog box.
Use the Drop Down List
Select the cell with the data validation list, and click the drop down arrow. If the cell does not contain the text “Full List”, the drop down will show the short list of top customers.
If you need to see the full list, select “Full List” from the top of the drop down list, and then click the drop down arrow again.
Watch the Video
To see the steps for creating a variable length drop down list, watch this short Excel video tutorial.
Watch in YouTube: Select Short or Long Drop Down List in Excel