How to create an if statement for a validation list?

Microsoft Excel 2013 32/64-bit
January 28, 2015 at 11:29:45
Specs: Windows 7
I have a spreadsheet that tracks students. On sheet 1 is the complete of students.
On sheet 2 is a report.

I need a formula for a dropdown that will look at a named range and return a list of names to the dropdown.

Example: IF(StudentClass="Nursery) a list of resulting names are displayed in the validation dropdown.


See More: How to create an if statement for a validation list?

Report •


#1
January 28, 2015 at 12:41:38
Here is a short video on Dependent Data Validation
which will show you how to accomplish what you want:

http://www.contextures.com/xlDataVa...

MIKE

http://www.skeptic.com/


Report •

#2
January 28, 2015 at 12:44:06
That only applies if both fields are data validation lists.

Report •

#3
January 28, 2015 at 13:24:56
OK, not sure about your question.

You stated you wanted a dropdown which to me, means a Data Validation list.

You then stated, as an example: IF(StudentClass="Nursery)
which I took to me mean, that there can be other StutdenClass besides Nursery, which equates to a Dependent Data Validation.

Is all you want a simple drop down of all the Nursery Students?

If you have already given the student list a name, IE Nursery
then select the cell where you want to drop down list to appear
and on the ribbon select Data Validation,
In the Data Validation window under Validation Criteria
change it from Any Value to List
In the Sources box, enter your Named Range, IE =Nursery

Is that what your looking for?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
January 28, 2015 at 13:50:06
I have a named range called StudentClass which has multiple options. (Nursery, 1st Grade, etc.) It is located on a sheet named drop downs. I have a validation for that and it works based off of that list.

Now I want a second validation list that looks through the list of students, which btw is a named range called StudentList, and return all of the names where StudentClass = Nursery.

I have a sample of this spreadsheet on Experts-Exchange if you have an account. I can post a link to the thread here if needed.

so yes I want a dropdown that will list all of the student names from my student list where class = nursery.

message edited by mwood


Report •

#5
January 28, 2015 at 13:59:53
I'm missing something here.

I want a second validation list that looks through the list of students, and return all of the names where StudentClass = Nursery.

You have that using Data Validation and your Named Range.

Or do you want a List of Names,
to be placed in individual cells,
that correspond to the Named Range of Nursery.

MIKE

http://www.skeptic.com/


Report •

#6
January 28, 2015 at 14:11:56
Let me see if I can explain this better.

let's say that on sheet 1 I have a named range called ID.

So on sheet 1 I want a dropdown that will display all names where id is equal to d-????.

that is what I am trying to do. the names of the individuals are in a named range on sheet 1 called StudentNames

so the dropdown list simply returns a list of names where id = d-????


Report •

#7
January 28, 2015 at 14:35:22
So your looking for some type of Autocomplete in the drop down?
You enter the first letter of a name and all the corresponding names are listed?

MIKE

http://www.skeptic.com/


Report •

#8
January 28, 2015 at 14:40:40
my id numbers are all different d-001 or it could be t-001 and so on.

I only want to return the names where id = d-whatever


Report •

#9
January 28, 2015 at 14:41:56
my id numbers are all different d-001 or it could be t-001 and so on.

I only want to return the names where id = d-whatever without having to enter the first letter.


Report •

#10
January 28, 2015 at 15:00:39
without having to enter the first letter.

How is excel going to know you only want ID's that equal D?
You need to input something in the Validation cell.

I think perhaps if you explain what it is your trying to do,
it might make more sense to me.
What is the reasoning behind your questions?

If all you want is a list, then perhaps a VLOOKUP() is all you need.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
January 29, 2015 at 07:01:34
you may be exactly right I'm just not sure how to make this one work.

if I use this =VLOOKUP("d-????",AttendeeList,30,FALSE) it will show me the first name with the ID number that starts with d-whatever.

if I put it into a validation dropdown I get "The list source must be a delimited list, or a reference to a single row or column.

message edited by mwood


Report •

#12
January 29, 2015 at 11:03:58
Jumping in late here, but having read all the posts so far, I'm as confused as Mike.

You went from saying "return all of the names where StudentClass = Nursery." to "I only want to return the names where id = d-whatever without having to enter the first letter."

Does the following site help? It will allow you to create a distinct list of values based on some search criteria. Once you have that list in a range of cells, you can create a Data Validation Drop Down that refers to that range.

You just have to deal with the #N/A errors if your lists will be of different lengths.

http://www.get-digital-help.com/201...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question