multiple dependent drop down lists
in [Functions]
|
From: Mikej on 14 Apr 2010 11:42 Hi, I am trying to implement multiple cascading drop down lists. I have two worksheets: a user one (with the dropdown lists and data tobe displayed) and a master one (with variables and data). on the master sheet, variables may be identical in a column but combination of all variables in a row is unique. Here's an example of my data worksheet (I use non standard characters and spaces in each column) var1 var2 var3 data1 data2 data3 A AA AAA 1 2 3 A AA BBB 4 5 6 A BB AAA 7 8 9 B BB AAA 10 11 12 B BB BBB 13 14 15 C AA CCC 16 17 18 C CC CCC 19 20 21 displaying the data on the user sheet works fine using basic data validation and DGET as long as I know what to selet from the lists. I'd like to be able to select var1(showing unique entries) for var2 to show me unique entries dependent on var1 and so on. in the end i will have over 6 variables to work with but that shouldn't make any difference. Thanks for any help.
From: Per Jessen on 14 Apr 2010 13:11 Hi Look at this site: http://www.contextures.com/xlDataVal02.html Regards, Per "Mikej" <Mikej(a)discussions.microsoft.com> skrev i meddelelsen news:5005BD96-E2DB-429E-A882-C892557261FE(a)microsoft.com... > Hi, > > I am trying to implement multiple cascading drop down lists. I have two > worksheets: a user one (with the dropdown lists and data tobe displayed) > and > a master one (with variables and data). > > on the master sheet, variables may be identical in a column but > combination > of all variables in a row is unique. > > Here's an example of my data worksheet (I use non standard characters and > spaces in each column) > > var1 var2 var3 data1 data2 data3 > A AA AAA 1 2 3 > A AA BBB 4 5 6 > A BB AAA 7 8 9 > B BB AAA 10 11 12 > B BB BBB 13 14 15 > C AA CCC 16 17 18 > C CC CCC 19 20 21 > > displaying the data on the user sheet works fine using basic data > validation > and DGET as long as I know what to selet from the lists. I'd like to be > able > to select var1(showing unique entries) for var2 to show me unique entries > dependent on var1 and so on. > > in the end i will have over 6 variables to work with but that shouldn't > make > any difference. > > Thanks for any help.
From: Mikej on 14 Apr 2010 15:23 Hi Per, I had already looked at that page but the data is not organised in the same way as I need to organise mine so it didn't enlight me. Having never looked at functions in excel until last week, I am far from being an expert.....I don't understand how I should approach my problem.
From: Duke Carey on 14 Apr 2010 15:55 Based on your sample data, they are NOT dependent, they are independent. For them to be DEPENDENT, then the options for var2 would be unique for each selection of var1, and so forth. So, if the first list offered a choice of Fruits and Veggies, after the user selected Fruits the second one would offer, say Citrus and nonCitrus. Choosing Citrus would limit the 3rd to oranges, lemons, etc. What you may be struggling with is how to bring back the data after ths user selects 3 unique vars, and that requires an array formula that MATCHes the three selected vars with the three columns of vars, maybe like this, which you'd enter by pressing Ctrl-Shift-Enter. That will tell you what row in your table contains the unique combination of the 3 vars the user selected. You'd use that row number with the INDEX() function to pull back the row of data elements =MATCH(var1&var2&var3,lookupvar1&lookupvar2&lookupvar3,0) "Mikej" wrote: > Hi, > > I am trying to implement multiple cascading drop down lists. I have two > worksheets: a user one (with the dropdown lists and data tobe displayed) and > a master one (with variables and data). > > on the master sheet, variables may be identical in a column but combination > of all variables in a row is unique. > > Here's an example of my data worksheet (I use non standard characters and > spaces in each column) > > var1 var2 var3 data1 data2 data3 > A AA AAA 1 2 3 > A AA BBB 4 5 6 > A BB AAA 7 8 9 > B BB AAA 10 11 12 > B BB BBB 13 14 15 > C AA CCC 16 17 18 > C CC CCC 19 20 21 > > displaying the data on the user sheet works fine using basic data validation > and DGET as long as I know what to selet from the lists. I'd like to be able > to select var1(showing unique entries) for var2 to show me unique entries > dependent on var1 and so on. > > in the end i will have over 6 variables to work with but that shouldn't make > any difference. > > Thanks for any help.
From: Mikej on 15 Apr 2010 04:05
I actually managed to find a function to pull the data depending on the variables selected: DGET(master!$A$1:$F$8,B12,$A$5:$C$6)) I'm really stuck with the selection drop down menus. I've managed to kind of get something to work with OFFSET for var2 (except that the options displayed in the drop down menu may not be unique) =OFFSET(data_start,MATCH(A6,var1_column,0)-1,1,COUNTIF(var1_column,A6),1) I have no idea on how to apply this to var3 to make it dependent on selections from var1 and var2. I'm not sure whether this is the right direction to go into. |