My husband asked me awhile back how to choose one option from a validation list and only show the options for that choice in another list. In order words, he would like to change a list’s values based on what is selected in another list. I was stumped for a brief moment. Then I remembered that Chandoo.org may help. And sure enough the answer came to us from Chandoo.  This is a great site to help you when you a faced with a difficult task to perform in Excel.

  • The following is a list that contains areas with associated projects.
  • Marketing has Horizon2010, Bingo 19x, Cosmo, and Spring2009 projects and the other areas are designated to other projects.
  • Creating validation lists are easy but you want to control what one person enters in one list determines what is shown in the second list.  For example, if one employee is in the Ops area, then that person can be on one of the three projects (Kayak, Karmic On, or Chain2Customer) and you want the second list ONLY to display those three projects.
  • When you create a validation list to display the 4 areas, Excel will display a drop down arrow to the right of the cell to display the 4 areas that you typed in the validation rule as shown below in the Select Area column. You then can autofill this cell down to the end of the table to perform this validation rule.

ggg

  • Go to data validation (menu > data > validation). Here is what the validation menu looks like:pic3

 

  • Then, I want Excel to show me in the Select Project column ONLY the Ops choices for the project that person is working on.  I created the Area and Project list and placed it near the table to make it easier for you to understand. In reality, I would have typed this information away from my main table on the SAME sheet.  You need to created a validation rule in order for the Select Project to display only the infomation pertaining to the Select Area.
  • Use OFFSET and MATCH to determine which range to use. Assuming the list of projects for each department is in a range G4:G16 with column B having the Area name and column C having the Project name and the list is sorted on column G, we can use OFFSET() and MATCH() combination along with COUNTIF to determine which range to use for project cell drop-down.
    • For the department cell, we can use simple list validation with values as “Marketing, Ops, Sales, IT”
    • For project cell, go to data validation (menu > data > validation) and specify a formula like this:
      =OFFSET($H$3,MATCH($B4,$G$4:$G$16,0),0,COUNTIF($G$4:$G$16,$B4),1)
    • (you must type the above formula in the Source location as seen below…it does not show the entire formula)
    • What is above formula doing? It is fetching a sub-range from the by finding where the first entry for the selected department is, returning x number of rows from that point, where x = no. of projects in that department.
    • Autofill this formula and since there is a Mixed Addressing (Relative and Absolute together) it makes it easier to copy down.

solution 2solution

 

  • Notice that choosing Ops ONLY shows you the three projects for that area!. Now it is easier to select both Area and Project! That is all. You now have a list drop-down that changes values based on what is selected in an earlier cell.
  • The following diagram show how easy it is to pick and choose.

soluton4

 

Advertisements