Excel Hints & Tips – Dependent Data Validation

In: BlogDate: Jun 26, 2013By: Ian Balboa

We all love Data Validation, right?

It’s a great way to make data entry easier, and to avoid spelling mistakes that lead to inconsistent values when analysing your data. In this blog we will take data validation a step further and look at how to make one drop-down list change its values depending on the choice made in a previous drop-down. We will be working with this sales data as an example:

 

 

Let’s assume we have set up data validation on the Area field so that we can choose from North or South:

 

 

What we would like is for the drop-down in the Centre field to show us only centres from the North, or only centres from the South, dependent upon our initial choice:

 

 

To do this, we have to first set up some named ranges that contain the appropriate values:

 

 

The range names you use must much the values from the first list. When the first value is chosen, this can then be used to create the range name to specify the dependent list. To do this, you must use the INDIRECT function within the Source box of the Data Validation pop-up:

 

NOTE: When you write the INDIRECT function, you must remember to remove the dollar sign from the row part of the cell reference; otherwise it will always refer to the same cell and will not pick up the correct value for that row.

The INDIRECT function takes the value from column A and turns it into a range name. The values in this range are then used as the source for the data validation list.

Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

How can we help you and your team to find joy in at least 80% of your work?

More about Happy

 

Why choose Happy?

  • Award-winning learner-focused approach
  • Small class sizes to give personal one-to-one support
  • Two years of free support after your course
  • Private group options available
  • No quibble money-back guarantee on all courses
5 out of 5 stars

It was really insightful content with practical tips to use at work and everyday life.

Sarah Maynard28 days ago

Ian Balboa

Ian was an IT trainer with Happy from 2007 to 2018, first as a member of staff and then as an associate. He delivered IT training in Microsoft Office (Word, Excel, PowerPoint, Access, Outlook) as well as VBA, SQL, HTML and Photoshop.

More by Ian

Want to learn more?

Is this your bag, baby? Then check out our fast-paced Analysing Excel Data for Better Business Decisions training course – or our Excel Level 3 training courses.