SubscribeAdvanced Data Validation Techniques in Excel [spreadcheats]
Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.
Problem 1: You have 2 lists of possible values and you want a way to switch between both
PS: Many thanks to Alex who proposed this idea and solution through e-mail.
You have a cell where user can enter any value from 2 lists. But you don’t want to overload the in-cell drop down list with tons of values, and rather prefer a simpler approach like this:
Solution: Use an IF() formula in validation criteria
The solution is to use an if() formula to determine which one of the two ranges should be used to validate cell contents.
- Select the cell where you want to have this type of validation
- Go to menu > data > validation
- In the criteria area, select “allow” as “list”
- In the source area, specify a formula like this:
=IF($B$7="Full List",Full-list-range,Partial-list-range)
That is all, you now have a data validation list that can change its source based on user preference.
Problem 2 : You would like to change a list’s values based on what is selected in another list
PS: Many thanks to Catherine for asking this question through email
You have a status tracking spreadsheet where each employee enters the status for each of the projects they are working on. They enter the status by first selecting the department and then selecting a project (from that department).
So how do you do this in Excel?
Solution: Use OFFSET and MATCH to determine which range to use
Remember the offset() and match() formulas we discussed in the last spreadcheats? Assuming the list of projects for each department is in a range B10:C22 with column B having the department name and column C having the project name and the list is sorted on column B, we can use offset() and match() combination along with countif (ahem!) 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(C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1) - 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.
That is all. You now have a list drop-down that changes values based on what is selected in an earlier cell.
Still having doubts?
Feel free to download this example workbook containing a tutorial on Advanced Data Validation in Excel and poke around to learn.
Subscribe to PHD to become supergood in day to day Excel in 30 days
Our spreadcheats series of posts will help you become good & productive in day to day MS Excel usage. Please subscribe to my RSS feed to get fresh new posts on excel
Trackbacks & Pingbacks
- Pingback by What level of input Data Validation is needed in Excel? | Corality Blog on July 9, 2009 @ 6:29 am
- Pingback by 31 Excel Tutorials – Learn and Be Awesome in Excel | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on May 2, 2010 @ 7:53 am
Comments
RSS feed for comments on this post. TrackBack URI
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. 
Let me add that usually you should have those lists in a “control” or “support” sheet, creating a named range for each list. Then you could enter: If($B$7=”Full List”, range1, range2). Just a bit cleaner.
@Jorge: You are right… using a control sheet is always advisable.
@ Jorge & Chandoo == Thanx for nice idea. Without naming the range, control/list from other sheet does not work.
@ Jorge & Chandoo == Ref. Problem#2
If you make databse in following order of Area, it won’t work :
Marketing
Ops
Marketing
Sales
Sales
Marketing
i.e. all the similar areas are to have one after other OR need to sort on area.
Your comment pls !
@Ketan
That’s why it says in the solution that “the list is sorted on column B”.
@Lincoln: thanks…
@Ketan: you are right The list needs to be sorted as I have noted in the article.
There are some solutions involving array formulas (shudder) etc. to overcome this, but I always try to keep these things simple so that anyone can understand and use. As such I am no good at array formulas myself and don’t venture in to them unless they are the only option.
Since we are on the topic of named ranges (well sort of) can someone tell me an easy way to rename a named range? I have a report where it would be really helpful to use a named range in my vlookup but the range varies month to month..
And since it’s Thanksgiving, I wanted to say thanks to you Chandoo because people think I spend hours & hours researching how to do things when really most of my information comes from right here!!
I have a question….may b m asking for too much….can it work like we have on web pages…i wud illustrate it wid an example as to what actualy am lukin for….
suppose when we select deptt “Ops” then it should remove the value currently present in project value instantly…….(if it’s not of “Ops” deptt)
@Cheryl… thank you. did you try using indirect or offset functions in the named range definition. That way even though the names stays same, you can change the range it refers to by simply changing value in a control cell. Let me know if you have trouble in doing this. I can elaborate on this.
@Azmat: hmm.. resetting value on previous selection… I guess you can use VBA to get this effect. But you wouldnt probably want to use vba. I dont know other ways around this. Does any one know how to reset a data validation enabled field when some other cell changes?
I have not, but I know you did have a posting about those recently. I will check that out! Thank you.
Have a simpler version of this solutions.
1. Define a name range with name as “Department” and list containing “Marketing, Ops, Sales, IT”
For Ex: In D1 put the title as Department, D2 as Marketing, D3 as Ops and so on
2. For each of the projects define a named range with the department names.
E1 will have the title Manufacturing, E2 has project 1,project2 …
3. In Cell A1 use data >> Validation >> source = Department
4. In Cell B2 just use data >> Validation >> source =INDIRECT(A1)
Hey in all the above examples the ‘list’ is in the same Excel file. What will I do if my ‘list’ is another Excel file?
Why is it that if you extend the Range ($B$10:$B$22) to for example ($B$10:$B$33) it gives you wrong output?
@Darwin .. Are you sure you have edited the range in all places?
I used your sample Excel file to test the data and extend the rows up to B33, then in Data Validation>Source I try to change the formula to this: =OFFSET(C9,MATCH($B$6,$B$10:$B$33,0),0,COUNTIF(B10:B33,$B$6),1)
If I select Marketing, the resulting list includes Projects under Ops Area.
I sent you an email with your sample data and additional rows in your gmail.
@Darwin… Did you sort the list by department name? It seems to work fine for me.
Got it! I should have paid attention to the comments here. Thanks!
Can anybody tell me about calender of date option in data validation, when I wish to put any date then calender should appear on screen.
Your reply will be highly appreciated.
thanks and and regards,
Adnan
@Adnan… I think you have to use a bit of VBA to show calendar control to let user enter valid dates.
I’ve got a few columns of data where the next column need to refer to the previous as data vaildation eg. District, Area, Area Manager, Project No. etc. if i choose a district (North) it brings up only the Areas for North but how can I go beyond that so that in a next cell I can select the Area Manager for that Area & the Project numbers for that Area