Solved

Help with setting up drop down menus in excel 2010 - special drop downs

Posted on 2014-02-11
18
340 Views
Last Modified: 2014-02-20
Hello,
I need help setting up SPECIAL drop down menus in Excel 2010.
I want (some of my) drop down menus to only contain limited choices which are based completely on the choice made in the previous column. In other words - if you choose east coast in column one - then it will only have east coast states in column two - and will then only have cities in the next columns drop down menu from the state that was chosen in the last column........... ect.
And - Can this work the same way in Access or only in Excel?
I have made a small example excel page and attached it here.
Thanks,
Luoyadan
EXAMPLE-DROP-DOWNS.xlsx
0
Comment
Question by:Luoyadan
  • 10
  • 8
18 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39852124
I won't comment about Access, but in Excel you have asked a frequently asked question. Microsoft Excel MVP Debra Dalgleish has a series of web pages that describe different techniques for accomplishing what you want. The first link is the one that I think best fits your situation, though you will need to arrange your data as Ms. Dalgleish suggests. I post all three links because your sample workbook looks like it has made up data rather than your actual problem.
http://www.contextures.com/xlDataVal13.html  "Dependent Dropdowns from a Sorted List "
http://www.contextures.com/xlDataVal02.html "Create Dependent Drop Down Lists" using named lists with INDIRECT
http://www.contextures.com/xlDataVal15.html "Excel Data Validation -- Create Dependent Lists With INDEX" also uses named lists, only with INDEX
0
 

Author Comment

by:Luoyadan
ID: 39852169
Actually I had read this exact post (from the internet) before asking the question here - and I still needed to ask the question here because I could not understand the examples on this web page. It is not clear to me on that website at all.
I need some one to either explain it more clearly - OR give me a file which contains these actions so I can see exactly how they work.
Thanks,
Luoyadan
0
 
LVL 80

Expert Comment

by:byundt
ID: 39852203
Luoyadan,
I created three named ranges: Regions, States and Cities. Regions contains WEST COAST, MID-COUNTRY and EAST COAST. States is a two column list with the region in first column and state in the second (each state is only listed once). Cities is a two column list with the state in first column and city in the second (each city is only listed once).

I then used these three formulas for the source on the data validation:
=Regions
=OFFSET(INDEX(States,MATCH(A3,INDEX(States,,1),0),2),0,0,COUNTIF(INDEX(States,,1),A3))
=OFFSET(INDEX(Cities,MATCH(B3,INDEX(Cities,,1),0),2),0,0,COUNTIF(INDEX(Cities,,1),B3))

You can extend the degree of dependency indefinitely, adding one two-column named range for each additional dependent dropdown.
Brad
EXAMPLE-DROP-DOWNS-Q28362563.xlsx
0
 

Author Comment

by:Luoyadan
ID: 39853361
I have read your answer - Thank you very much! Especially thanks for such a speedy answer.
I will award this question to you as soon as I plug it into my spreadsheet and see the results.
This will most likely happen within 24 hours.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39853734
Luoyadan,
When you try to figure out the suggested formula, the bit with INDEX may be somewhat confusing.

INDEX is a very useful function because:
1.  It can return an entire row or column from a rectangular range of cells. To do this, don't specify a value for the row or column after the respective comma:
=INDEX(A1:Z100,,1)           returns A1:A100
=INDEX(A1:Z100,2,)           returns A2:Z2

2.  INDEX returns a range reference rather than the value. You can use the range reference to get a value, of course--INDEX(A1:Z100,2,3) will eventually get you the value of C2. But you can also use INDEX to build a range reference. For example, to get a range for a data validation dropdown, you might use the following:
=B2:INDEX(B2:B100,COUNTA(B2:B100))
The INDEX in that range reference will point to the last cell with data in a contiguous data range in column B. In other words, it ignores the blank cells at the bottom of the list.

Brad
0
 

Author Comment

by:Luoyadan
ID: 39853812
Brad,
I am by no means a good or average excel user - I would surely be considered a novice. So with that said - are you suggesting a "better solution"? Just reading your response does not tell me what it is you are saying. Once I have time to compare the two answers side by side - then perhaps I will then understand. So (for now) to simplify matters for me just tell me yes or no that you are offering a "better solution"?
Thanks,
Luoyadan
0
 
LVL 80

Expert Comment

by:byundt
ID: 39853973
Luoyadan,
I was trying to explain the suggested approach. Some people like to understand a solution in depth, while others are just glad that it works.

Suppose you wanted to understand this formula (for the first dependent dropdown):
=OFFSET(INDEX(States,MATCH(A3,INDEX(States,,1),0),2),0,0,COUNTIF(INDEX(States,,1),A3))

You would break the formula up into pieces, then try to understand each piece:
INDEX(States,,1)       returns the first column from the named range States
COUNTIF(INDEX(States,,1),A3)       counts how may times the selection in A3 occurs in that first column of States
MATCH(A3,INDEX(States,,1),0)      finds the "row number" where A3 is first found in the first column of States
=OFFSET(INDEX(States,MATCH(A3,INDEX(States,,1),0),2),0,0,COUNTIF(INDEX(States,,1),A3))
returns a range of cells from the second column of States. This range starts in the row where A3 was first found in the first column, and ends with the last row where A3 was found.

Not stated explicitly, but definitely shown in the data, the named range States must be sorted by the values in the first column. The formulas won't work at all if the tables aren't sorted.

Brad
0
 

Author Comment

by:Luoyadan
ID: 39857947
Hello,
I intend to award this question to you, so you have no reason to worry - and I am not purposely delaying closing/awarding this question. I had a fairly large amount of stuff thrown at me this week at work - so I haven't had time to even plug this in to my spreadsheet yet. I am trying to keep the question open until I have had time to do that - just in case something weird happens. But if too much times lapses - I will go ahead and award it to you even before I have tested it. But I do want to ask you if you can elaborate a little on this statement you made in your last post...........
"Not stated explicitly, but definitely shown in the data, the named range States must be sorted by the values in the first column. The formulas won't work at all if the tables aren't sorted"
How will or can this affect my spreadsheet? What do I need to be careful about?
Thanks,
Luoyadan
0
 
LVL 80

Expert Comment

by:byundt
ID: 39858031
"Not stated explicitly, but definitely shown in the data, the named range States must be sorted by the values in the first column. The formulas won't work at all if the tables aren't sorted"
The suggested formula is returning a contiguous range of cells from the second column. Those cells won't be contiguous if the table hasn't been previously sorted by its first column. The formula uses INDEX and MATCH to find the first cell in the range, and COUNTIF and INDEX to find the number of values to display in the dropdown. If the table isn't sorted by its first column, the dropdown will have a correct value at the top of the list, but may have an embarrassingly incorrect set of values for the remainder.

For dependent dropdowns, there are many ways to skin the cat. The most common are the ones described by Debra Dalgleish in the links in my opening Comment. I chose the two column list approach because it was better for the specific problem that you posed: many choices in the second dropdown. Two column lists don't care how many choices there are.

The other two methods Ms. Dalgleish described use a different named range for each choice in the second dropdown. With 50 states in the USA, you can appreciate that you would need to create a lot of named ranges to support your third dropdown.

If there are relatively few choices in the first and second dropdowns, then creating named ranges for each choice becomes more feasible. The advantage of that approach is that you can use single column lists for each named range. I'd suggest using INDIRECT if you had only a few sets of dependent dropdowns. I'd suggest using INDEX if you had many such sets. The reason is that INDIRECT is a volatile function, and will be evaluated every time that any cell is changed on any open worksheet. If your workbook contains a gazillion volatile formulas, the recalc time will be sluggish.

In addition to the three methods described by Ms. Dalgleish, I have also solved Experts Exchange problems using array-entered formulas and VBA functions to build the lists for the dropdowns. The complexity of these approaches is justified if you cannot sort a two column list by its first column.

I realize that your real problem may be different than the abstract problem described in your question. I hope you'll appreciate that I relied on certain features of Regions, States and Cities to make my recommendation--and that I might have suggested something completely different had I known the exact details of your real problem.

FWIW, close the thread at your convenience, but know that I continue to post in threads long after they have closed. You might have unforeseen problems with implementation, and it is better to keep the discussion all in the same thread for the benefit of future people searching the Experts Exchange database.

Brad
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Luoyadan
ID: 39858049
Here you mentioned the "INDIRECT FUNCTION".......... and I was surprised that your solution was not based on this "indirect function". I had actually found on the internet this "indirect function" and I thought it was/would be my solution. I played around with it but could not get it to work for me (for some reason). If I sent to you a list of the actual drop downs - are you saying that the "indirect function" may be the solution that works best for me? I will go ahead and attach a file here now in case you want to look at it. It is an excel file -  but it is not a spreadsheet OR the file in question - I just used excel as a word processor because it was easier to lay out my diagram of these drop down menus.
I did not create this file just for you - I created it as instructions for a potential programmer (I was considering hiring)  - that I finally gave up on. Anyway... this page may help you see what I am trying to do. You should focus on the first section where it mentions "PRIZE IN" (the first part of the page). If you have trouble understanding the layout of the drop downs - ask and I will explain.
Thanks,
Luoyadan
0
 
LVL 80

Expert Comment

by:byundt
ID: 39858151
Luoyadan,
I assume the file attachment is forthcoming.

Brad
0
 

Author Comment

by:Luoyadan
ID: 39859378
I thought I attached the file............ sorry!
DATABASE-INFO-FOR-PROGRAMMERS.xlsx
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39860739
Luoyadan,
The workbook you posted falls in a similar category to Regions...States...Cities. You need to create a large number of named ranges to use the INDIRECT method, so I would recommend a series of two column lists instead.

So you can see how INDIRECT might work, I created the named ranges for the first four choices under Prize Category. Since the names of named ranges cannot include an embedded character, I used SUBSTITUTE inside the INDIRECT to delete them. I also noticed that the same manufacturers appeared in multiple Prize Categories. So I prefixed the respective names with the Prize Category, e.g. PortableSystemApple.

The attached workbook (see worksheet "Dependent dropdowns INDIRECT") is using the following formulas for Data Validation source:
=PrizeCategories
=INDIRECT(SUBSTITUTE($A2," ","") & B$1)
=INDIRECT(SUBSTITUTE($A2," ","") & $B2)

Note that you will get an error alert when setting up the data validations ("the formula currently evaluates to an error"). I ignored the warning, and verified that the dropdowns were working.

In the attached workbook, I used fixed named ranges rather than dynamic ones. I don't know if you will be adding/deleting choices in your named ranges. If you expect to do so, a dynamic named range will expand and contract with the amount of data. You can set one up using a "refers to" formula like:
=$B$2:INDEX($B$2:$B$10,COUNTA($B$2:$B$10))

Brad
DATABASE-INFO-FOR-PROGRAMMERS-Q2.xlsx
0
 

Author Comment

by:Luoyadan
ID: 39861471
Brad,

If you noticed in my example I gave you - the excel sheet I used to help explain what would go in the drop down menus - at the bottom of just about every group for the drop downs I included " (new entry) ". This is a feature that would help me a great deal when I need to add additional choices to the drop down menus. I don't know if this can only be done with VB code or if it can be accomplished another way. I really need to be able to add to these drop down menus as my company expands the prizes purchased and used. If the logical choice is to  simply edit the lists within the excel pages - then that is what I will do. But I wanted to ask you if I had other options available to me?

Thanks,
Luoyadan
0
 
LVL 80

Expert Comment

by:byundt
ID: 39861563
Luoyadan,
You can append new items to the bottom of the lists for the last dropdown (Model) if you use dynamic named ranges as described in my previous Comment.

You can also append new items to the bottom of lists for intermediate dropdowns (e.g. Manufacturer or Prize Category) using dynamic named ranges, but you will need to create the associated named ranges for their dependents manually.

Although you can automate the creation of the named ranges using VBA code, I think it is much better for you to use the two column lists as described in my earlier Comments for Regions, States and Cities. By all means make Regions, States and Cities dynamic named ranges, and make sure that you sort States and Cities by their first column. If you do so, then no changes need to be made to any of the data validation dropdowns when you add new States or Cities.

Brad
0
 
LVL 80

Expert Comment

by:byundt
ID: 39862321
Luoyadan,
I modified the Regions, States & Cities named ranges in the attached workbook to use dynamic named ranges as suggested in my previous Comment. You can now add as many items to each list as you like, and the dropdowns will automatically reflect them.

Here are the "refers to" formulas for Regions, States and Cities. You can find (and set) them in the Formulas...Name Manager menu item.
=Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$1000,COUNTA(Sheet2!$A$2:$A$1000))
=Sheet2!$C$2:INDEX(Sheet2!$D$2:$D$1000,COUNTA(Sheet2!$C$2:$C$1000))
=Sheet2!$F$2:INDEX(Sheet2!$G$2:$G$1000,COUNTA(Sheet2!$F$2:$F$1000))

When adding or deleting items to the lists, Just follow these simple rules:
1.  Don't put any blank cells in first column of each list. Any such blank cells in the middle of your data will make the dropdowns stop short of listing all the items they are supposed to.
2.  Make sure that you sort each list by its first column. If you don't, the associated dropdowns will have unexpected items in the displayed list.
3.  You will need to update the formulas if you put more than a thousand rows in the named ranges. I thought a thousand would be plenty--but your employer might surprise me.

Brad
EXAMPLE-DROP-DOWNS-Q28362563.xlsx
0
 

Author Closing Comment

by:Luoyadan
ID: 39875776
I have unfortunately had a rush of work on my desk this last week - I have even been bringing my work home and still can't catch up. I have not had the time I needed to test these suggestions - and I cannot expect the expert to wait for me to test the answers. So the only solution is go ahead and award this answer - with hopes if I have additional questions the expert will help me.
You have done an outstanding job with answering my question... and even other questions that came into play the more we communicated. I want to thank you very much for the large amount of time you devoted to this topic as well as the very helpful answers.
Thanks Again!!!
Luoyadan
0
 
LVL 80

Expert Comment

by:byundt
ID: 39875827
Luoyadan,
Please continue to post in this thread if you have questions regarding implementation of the suggestions made. Doing so makes the thread more valuable to future users of the Experts Exchange database.

Brad
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now