Free Excel
|||

INDIRECT function to create conditional lists

Some people wonder what they can use the INDIRECT function for in Excel. Well when it comes to data validation it can be pretty handy. Say for instance you want any selection in a Data Validation drop down list to be filtered by the value in a previous list. In the following exercise, which we will do from scratch, we will filter the models of cars dependant on the intial make selection. So if you choose Ford from the make list, then you can only choose Ford models like the Focus or Mondeo.

Drop down menus

  1. Open a new Excel document.
  2. On a second sheet enter in the following:

    Ford

    Focus i3 488 GTB
    BMW Mondeo M3 La Ferrari
    Ferrari Mustang X5 California T
        3 Series 458 Speciale
  3. Highlight the list of car makes, cells A1:A3 and name the range Make. (You can use the Name Box at the top left to do this).
  4. Name the other ranges as follows:
    • B1:B3 - Ford
    • C1:C4 - BMW
    • D1:D4 - Ferrari

  5. Ensure that each is spelt correctly. You should now have four named ranges (Make, Ford, BMW, Ferrari)
  6. On Sheet1 click in cell A1 and type Make.
  7. Then in cell B1 insert a data validation drop down list using the field name make as the source of the list.
    Data validation list box using a named range
  8. Now type Model in Cell C1.
  9. Add another dropdown validation list in D1, but this time for the Source Enter the following:
    =INDIRECT($B$1)
    You can click on B1 if you prefer. The INDIRECT function will subsitute the cell reference in the brackets for the contents of the cell. In other words when you choose Ford from the make drop down list the INDIRECT function will use the Ford named range for the list. If you choose Ferrari INDIRECT will choose the Ferrari named list.
  10. Click OK you may get an error message, this is to be expected, just click OK and you're done. Now to test your work.
  11. Click in Cell B1 and you'll note that there is a drop down menu, click on the menu and choose Ferrari.
  12. Now click in D1 and click the drop down menu and note that Ferrari models are shown. (Ahhh the only car one should drive).