Solved

How to set up list(s) for 3-level dependent data validation lists?

Posted on 2014-02-20
3
386 Views
Last Modified: 2014-02-24
Hello - I'm an Access developer that has been tasked with spiffing up a spreadsheet that holds employee rewards. It has the expected data: division, department, reward area, reward type, etc...

It wasn't a problem to set up the Division-Department table for dependent data validation lists, but now they are telling me they want to add a 3rd level of "Section".

so, is there a smart way to set up for a 3-level grid?

If it's just a single 3-column grid, how do you keep from getting Dept values in the Dept DVL that are repeated for every Section element?

If it's 2 2-column grids of Division-Departments, and then Departments-Sections, then the user has to keep it straight as time goes by and they have to maintain those grids.

Thanks
0
Comment
Question by:mlagrange
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Dreamboat earned 500 total points
ID: 39875499
I don't personally know how to do it offhand; however, they call this "conditional" or "cascading" drop downs.

Here's just one MVP's solution.

http://www.contextures.com/xlDataVal02.html

Debra specifically demonstrates a 3rd list. Just scroll down about halfway.
0
 

Author Comment

by:mlagrange
ID: 39883060
Thanks - I my situation was a more complicated, but your link led me to:

http://blog.contextures.com/archives/2010/03/31/dependent-data-validation-from-pivot-tables/

And I was able to base off of that. As one of the commenters noted, her (his?) solution was geared for Excel 2003, and it's a little easier now that you can "Repeat All Item Labels", so you don't have to find the last matching item, just the first one. I was able to get by with a combination of OFFSET(), MATCH() and COUNTIF()

Thanks again
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 39884325
You're welcome. Glad you got it sorted.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

856 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