multiple dependent drop down lists

[Mikej]

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.

 

[Per Jessen]

Hi

Look at this site:

http://www.contextures.com/xlDataVal02.html

Regards,
Per

 

[Mikej]

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.

 

[Duke Carey]

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]

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.

 

[Duke Carey]

OK, now I see what you want to do. You want to DYNAMICALLY filter var2 based
on var1's value.

There's no easy way to do this in Excel, and there may be no way without
resorting to non-trivial VBA code.

This might be something that you could adapt. It talks about using
parameterized queries in Excel to pull data back from Access. You can also
use Excel as the 'database.' I'd see it as you using the distinct var1
values in the first list. The selected value becomes the parameter to the
query that finds the distinct var2 values associated with the selected var1,
and finally, use selected var1 and var2 values to find the distinct var3
values. Finally, all three become paramters in the final query that returns
data

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

 

[Mikej]

Thanks for all the suggestions. I'll look at that link and see how I can
integrate VBA. I'll try to adapt this within the next few days and keep you
posted.