How to Improve Your Excel Spreadsheets With Data Validation
Have you ever seen an Excel spreadsheet with a dropdown list in it and wondered how it was done?
This is data validation. Data validation improves the data in your spreadsheets. It makes reporting much easier as there is no risk of typos and no risk of an entry not fitting into your criteria.
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.
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your people to find joy in at least 80% of your work?
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.
Why not sign up to our newsletter?
Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.
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.
Testimonials from happy Customers
I found the Office 365 training really useful. It consolidated my self-taught learning and showed me some new things that will make my life easier as well as doing some t...
Found the session interactive and engaging throughout. Range of tools shared.
Helen Twigg1 day ago
Really enjoyed the Liberating Structures Online Immersion Workshop. High energy that kept you involved all day and have been able to use the learning immediately. Woul...
Ruth Gibbins1 day ago
Great facilitation, very interactive and organsied
Emma Willcox1 day ago
Great course, with lots of opportunity to practice techniques and not just learn about them. The day is packed full of content, so be ready to embrace the fast pace and t...
Judy Duff1 day ago
Happy are a creative, unique, relevant and modern-day approachers of workshop delivery and content.
Trusted Customer3 days ago
Excellent course and facilitator :) The course had good interactive sessions and well thought out content. It was made relevant to our roles and the trainer was supportiv...
Scott Haines4 days ago
I thought the training was excellent. It was really enjoyable and engaging and gave me lots of ideas to take away.
Toks Kinoshi4 days ago
An excellent session led by Henry where I learnt some new, refreshing techniques
Trusted Customer7 days ago
no documentation - forgotten the basics immediately
Janet Doran7 days ago
This webinar really delivered on making zoom interactive. Henry didn't tell us how to do it he let us experience it through his engaging style and pace, use of zoom featu...
Trusted Customer7 days ago
Fun and engsging