Drop Down Menus

Hello

I am wondering if the following is possible and if it is, if you can help point me in the right direction:

I want to have a spread sheet where a user will pick an option from a drop down list, then based on that option choice, have a different cell populate with a specifc drop down menu.

Example:

Cell A1 has a drop down with three choices (1,2,3)
User selects option 1
Cell B1 will now have a drop down with options A,B,C
However:
If user selects option 2 from A1
Cell B1 will now have a drop down menu with options X,Y,Z

Is this possible? What is the type of function / formula called to make this happen?

Thanks!

Yes, you'll need to name each list (Insert>Name>Define) and use the INDIRECT function. Take a look at this example.

ChemistB

Hi I am sorry I cant download files from where I am at.

Can you give an example of the command string using the example I gave?

Please keep in mind my understanding of higher excel functions is limited.

Thank you

Cell A1 has a drop down with three choices (1,2,3)
User selects option 1
Cell B1 will now have a drop down with options A,B,C
However:
If user selects option 2 from A1
Cell B1 will now have a drop down menu with options X,Y,Z

Are you using Data Validation to create your dropdown lists? My solution uses Data validation rather than Control toolbox dropdowns.

Do you know how to name ranges?

ChemistB

Yes, I am using data validation to create a total of three lists. They are built as

List 1 = A,B,C
List 2 = D,E,F
List 3 = G,H,I

Then I want a master cell that the user will choose an option

Master Cell = List 1, List 2, List 3 (Placed in A1)

The cell to populate the appropriate list is B1

Okay, you didn't answer my question about whether you know how to define names so I will assume that you do. Let's say you named them List1, List2, List3 (defined names can't have spaces) and your initial drop down is List 1, List 2, List 3.
In B1, you'll just need to use the indirect formula and take out the blanks

Please Login or Register  to view this content.

The Substitute replaces the blanks

ChemistB

I apologize, I do not know how to define names, but my list naming conventions were incorrect since I did not know you cant use spaces. I ahve since remade them.

Can you exaplain "define name"

If your "lists" do not change size. (e.g. if list2 will always be in D2:D7), then the easiest way to define a name is to highlight the range (don't include any headers) and in the "Name Box" (small white box in upper left where it normally has the cell identification) click and type list2. To test this, click anywhere else in the spreadsheet and then click on the namebox dropdown menu and you'll see "list2". Click on it and list2 will be highlighted/selected.

Your master list (dropdown menu in A1) can have spaces as long as you use the INDIRECT/Substitute formula in B1.

Let us know if you have any problems with this.

ChemistB

Ok, I have done the list naming exercise you told me to do and that works. I made three lists and named the cell the drop down menu resides in as List1, List2 and List3. When I click on an empty cell and use that naming drop down in the upper left corner, all three names are there and if I select one, the cell highlight moves to that area.

What I am confused on is how to fill in the formula so it works:

You gave me
=INDIRECT(SUBSTITUTE(A1," ",""))

I am not sure what goes in the quotes. Also the Excel tool tip is displaying this when I am typing items into the formula

SUBSTITUTE(text, old_text, new_text, [instance_num]))

What is the instance_num and does anything go between the very last )) on the end of the forumula

I am not sure what goes in the quotes.

The formula can go in exactly as written. " " is a blank (that's the old text),"" is nothing or no blank (that's the new text) effectively removing all your blanks.

Also the Excel tool tip is displaying this when I am typing items into the formula SUBSTITUTE(text, old_text, new_text, [instance_num]))

What is the instance_num and does anything go between the very last )) on the end of the forumula

In some cases, you might want to only substitute a specific instance of something in a string (e.g. take out the first blank or the second dash). If you do not fill in this field, which I didn't, then it removes all instances of your old text. Make sense?

Chemist B