• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

i need a formula for a validation dropdown in Excel 2013

I need a formula that will read one cell and if the value is correct return the results to a validation dropdown.

EXAMPLE: =IF(StudentClass="1st Grade") It will list the StudentNames in the dropdown

The StudentClass and StudentNames are Named Areas on the spreadsheet
0
Mark Wood
Asked:
Mark Wood
  • 5
  • 2
1 Solution
 
James ElliottManaging DirectorCommented:
Here's a great tutorial on exactly this requirement:

http://www.contextures.com/xlDataVal02.html
0
 
Mark WoodNetwork AdministratorAuthor Commented:
I looked at that but I guess I am not getting it so let me explain a little more.

On sheet 1 I have a list of student with 2 named ranges. 1 is StudentName and the other is StudentClass.
On Sheet 2 I have a form to track attendance where I want to be able to have a dropdown that will display only the StudentNames where StudentClass = Nursery.

I will create attendance pages for each class so that sheet 3 for example would have a dropdown list where it would have a dropdown list that will display only the StudentNames where StudentClass = 1st Grade.

and so on.
0
 
James ElliottManaging DirectorCommented:
It would be best if you structured your first sheet into columns for each class. Like the example attached.

If you name your ranges the same as the column headers, then the formula for the second drop down is
 
=INDIRECT(TheRangeOfYourFirstDropDown)

Open in new window

ee.xlsx
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Mark WoodNetwork AdministratorAuthor Commented:
This is the file I am using. There will be a tab for each class.
Wednesday-Attendance.xlsx
0
 
Mark WoodNetwork AdministratorAuthor Commented:
Can someone please help with this.

The first list is not a dropdown it is a named range called "StudentClass".

So IF(StudentClass=Nursery) the dropdown validation will list those names.
0
 
Mark WoodNetwork AdministratorAuthor Commented:
this may require a vlookup as opposed to an if statement, I'm not sure.
0
 
Mark WoodNetwork AdministratorAuthor Commented:
I went about it a different way but thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now