Solved

i need a formula for a validation dropdown in Excel 2013

Posted on 2015-01-18
7
67 Views
Last Modified: 2015-02-03
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
Comment
Question by:Mark Wood
  • 5
  • 2
7 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40557353
Here's a great tutorial on exactly this requirement:

http://www.contextures.com/xlDataVal02.html
0
 
LVL 2

Author Comment

by:Mark Wood
ID: 40557810
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
 
LVL 12

Accepted Solution

by:
James Elliott earned 500 total points
ID: 40557847
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Author Comment

by:Mark Wood
ID: 40558048
This is the file I am using. There will be a tab for each class.
Wednesday-Attendance.xlsx
0
 
LVL 2

Author Comment

by:Mark Wood
ID: 40575876
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
 
LVL 2

Author Comment

by:Mark Wood
ID: 40577458
this may require a vlookup as opposed to an if statement, I'm not sure.
0
 
LVL 2

Author Closing Comment

by:Mark Wood
ID: 40587922
I went about it a different way but thanks for the help.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question