General discussion


lists in excel

By sohailabbasi ·
I am trying to create a database using excel for storing machine information on 3 different production lines.
I want to have 2 drop down lists. one for the user to select the production line and the other for the user to select the machine to filter out the required machine information on the required line. Now the case is that some machine names are different on the different production lines. is there a way in excel to display the contents of the second drop down list with relation to the production line selected on the 1st list in other words suppose if line A was selected than the contents(machine names) of the 2nd list would change automatically to match line A and if Line B is selected then the entries in the 2nd list match Line B.

much needed help,

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by IT_Cowboy In reply to lists in excel

There is a way without writing VBA code:

You will need to get a certain range of cells to return the values you want based on the selection in the first list.

I have managed to do a sample up quickly, would be much easier to email you the file rather than try to explain it here.

Here is a quick explain:

On Sheet2 I created a list of 3 values A1:A3 (Item1, Item2 and Item3). On Sheet1 (A1) I created a drop down list for this range.
Back onto Sheet2 I reference the value returned by the list (either Item1, Item2, Item3) into A6 (ie: = Sheet1!A1).
In Column B I entered a formula to check the value of the first list then show a value depending on the list. ie: =If(A6="Item1","Machine1.1",If(A6="Item2","Machine2.1","Machine3.1")

this means if Item1 is selected, Machine1.1 will be in the 2nd drop down list, if Item2 is selected, Machine2.1 will appear in the 2nd drop down list and so on.

You will need to add a row for each set of 2nd list items.

You range for the second list will be B6:B??

This sort of works but the list will need to be as long as the longest sub list and those shorter lists will display blanks at the bottom, however it achieved what you want at some level.

Note that if you select something from list 2 then select a different value in list 1, you will need to reselect list 2.

Collapse -

by DKlippert In reply to lists in excel

Just for kicks, play around with this layout I found in the forums:
1. Somewhere in the worksheet, create three named ranges
a) Type (elements of this named range should be Boy, Girl)
b) Boy (elements of this named range should be S, T, U)
c) Girl (elements of this named range should be X, Y, Z)

2. Select A1:A10, go to Data>Validation>List. Under source, type:

3. Select B1:B10, go to Data>Validation>List. Under source, type:

Related Discussions

Related Forums