In an earlier blog, I showed you how to create drop down lists and rules to control what people enter into your spreadsheets using DATA VALIDATION rules. These are a great tool to use and dramatically reduce the number of user input errors.
Going back to our timesheet example, we set up a number of drop down lists and rules, but there is still margin for error. For example, not every employee might work on every project and task combination. There are chargeable and non-chargeable activities but these are all in a single list. In both these examples, a user could correctly select their name (if they can’t do that then there is no hope!) or identify an activity as non-chargeable but select totally the wrong project/task combination or assign a chargeable activity. So although we have limited their choices, there is still lots of potential for messing things up.
To get around this problem we need to create linked or dependent validation drop down lists, where a choice made in one cell affects the choices available in the next.
To make this work, we need lots of NAMED RANGES.
Let’s take the chargeable/non-chargeable options as an example.
At the moment our drop down list in the Details column is using the named range Reasons but this contains every option – both chargeable and non-chargeable. First we need to split this into two separate lists – chargeable reasons and non-chargeable reasons.
Now, we need to name our two lists. When you name them you MUST use the values from the drop down list that will determine the options in list 2. In this example, the options in the first drop down are “Chargeable” and “Non-Chargeable”. Our two lists must therefore use those names for the linking to work.
Highlight the cells containing the chargeable items and then click into the NAME BOX and type in Chargeable. Repeat for the Non-Chargeable items. At this point however, you may get an error message.
Remember that when naming ranges you cannot have spaces or certain characters including dash (-) which we have here. This means we have to adjust our values in the first drop down to remove any spaces or dashes etc. If you don’t do this then you can’t link the two validation lists. Mildly irritating, but necessary.
In this example I will opt for an underscore to give us a value and matching named range of Non_Chargeable.
Once you have all your named ranges, all that’s required is to make it work!
Go back to the Timesheet and edit the existing validation rule in the Details column. Instead of referring to the full list of options stored in the named range “Reasons” we enter a formula…
Column D contains our drop down with the Chargeable/Non_Chargeable options. What the INDIRECT function does is look in cell D5 and then goes to find whatever it finds in that cell. In this instance, the only things called “Chargeable” or “Non_Chargeable” are named ranges. The values that make up the named range are then displayed in the drop down list.
So now when you make a choice in column D it determines what appears in column E.
Or if you select Non_Chargeable…
This has now added a new level of control to the worksheet, preventing users from making an incorrect selection following a selection in another column.
This method can be continued by creating new named ranges using the values from the named ranges we just created. Let’s say that after choosing “Chargeable” as my first option, I am then given the limited list of options that are chargeable. From that list I choose “Training”. Assuming we have another column, I would want a list of training courses to appear so that I can select from that list. Similar to what I did above, I would create a list of training courses and name it “Training” – an exact match for the value in my details drop down list. Repeat this process for each and every option in your drop down list. You could end up with dozens of named ranges but at least you will know that what you get back from users will be correct.