How do I make a drop down list pull up a specific list.

D

DidDoThat

New Member
Joined
Nov 1, 2006
Messages
3
Basically I need to display a specific table when a specific word appears in my drop down list.

I already have the dependent lists set up where as you pick a specific word from the drop down, and the next drop down only brings up choices relating to this word. To make this easier on the end user, I wish to have all the choices for the second drop down displayed at one time , as opposed to having a drop down menu with the choices, but I need them to change when ever you make a different selection. I need to get this working within the constraints of just Excel for right now, and then I will work on it with VB. Any suggestions would be greatly appreciated.

IE (This is what it looks like now)

Drop Down>(Fruit,Vegetables) And when you select Fruit you get the following

Drop Down>(Apples, Grapes, Oranges)

I need it to function like this

Drop Down>(Fruit,Vegetables) And when you select Fruit you get the following

Apples
Grapes
Oranges

and then when you use the Vegetable drop down menu it changes to

Peas
Corn
etc
etc.


Does this make sense? What am I doing wrong?

Thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi and welcome to the board.

I really don't believe you will be able to do this without VBA (I could however, be wrong). You could, I guess, use If statements to construct the second list, but that would be very cumbersome, writing an If statement for each item in the second list(s)

Sorry

lenze
 
Upvote 0
That's the feeling I have been getting, since Im not as well versed in VB I was hoping to avoid that. But I guess thats the path I will have to take.
 
Upvote 0
First list:
Choice
Fruit
Vegetables

Second list:
Fruit
Apples
Oranges
Cherries

Third List:
Vegetables
Tomatoes
Zucchini
Eggplant

Each list is a named range (use the name that I have selected in bold), ie every item on the Choice list has a matching range name with the items that correspond to it.

Let's say the first validation is in A3. It will have the list range =Choice

The second list is in C3. Its list formula is =INDIRECT(A3)

When you select Fruit, the contents of the Fruit list are displayed in the C3 dropdown. Etc...

Denis
 
Upvote 0
Denis, I have that part, this is where I'm getting confused. Here is a diagram

DropDownMenu (Fruit,Vegtables) Select Fruit (now below it I want the following to appear all at the same time, not in drop down form)

Grapes
Apples
Pears

Then I will do a data validation to whatever appears in those cells to reference something else, ie

Grapes (dropdownmenu)lbs,ounces,bunch
Apples (dropdownmenu)lbs,ounces,bushel
Pears (dropdownmenu)lbs,quantity,ounces

So the whole flow would be like this.

DropDownMenu (Fruit,Vegtables) Fruit

Grapes (dropdownmenu)lbs,ounces,bunch
Apples (dropdownmenu)lbs,ounces,bushel
Pears (dropdownmenu)lbs,quantity,ounces

I apologize if this is confusing, I am trying my hardest to finish this in excel because its where I started and the database is pretty big.
 
Upvote 0
I don't know how to achieve step 2 (show whole list without dropdwon) without using VBA. If you need to stay away from code, keep the dropdown lists going in a series. So...

As I mentioned before, then:

You will need a new named range for each item in the Fruit list and each item in the Vegetable list. If you go more than 3 steps deep there will be a LOT of lists!

The drop-down to pick the 3rd item (in my system) would have the formula
=INDIRECT(E3), where E3 is the location of the second drop-down.

Denis
 
Upvote 0
Kind of confused by the directions here.

Say for instance column one is a list of departments:
Mold
Grind
Core
Clean

I would like to set up a dropdown list if you select one of those departments, in another drop down list right next to it, you can see all available positions. I have a separate column listed with each department and positions under it. Each of those lists is named according to the department.

I have the first dropdown list set up. Now where I get confused is what VBA code I use or where i put it to make the second dropdown list match what positions are available to the matching department.
 
Upvote 0
Hello,

I'm following the instructions above, but when I try to enter =INDIRECT(C2) I get the message "The Source currently evaluates to an error". I have a drop-down in C2 and a second drop-down in D2, the choices in which I would like to be dependent on what is chosen in C2. Can you please advise?

Thanks in advance...!

EDIT: sorry, didn't notice the post directly above mine...think we're asking the same question though! :biggrin:
 
Last edited:
Upvote 0
guys,

you would better post your questions in your own thread
you can always link to this thread if you want

note: I am not a moderator

you can email me (see my signature) for some working examples of this kind of "cascading" lists

subject of email: "cascades"

kind regards,
Erik
 
Upvote 0
My problem with this procedure is that whilst it works fine with named ranges I get a 'Source currently evaluates to an error' message if I use dynamic named ranges instead.
Any ideas at all?

P.S. Sorry about the direct e-mail Erik.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Nested drop down boxes to choose items in a named range - a better way?
Replies
0
Views
241
RockandGrohl
R
C
  • Question
Dependent Drop Down Help, Excel 2010
Replies
5
Views
148
Akuini
Akuini
D
  • Solved
Drop down list to display only grouped items
2
Replies
11
Views
220
Peter_SSs
Peter_SSs
S
  • Solved
Question about Drop down list
Replies
3
Views
230
RoryA
RoryA
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
236
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top