A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

multiple dependent drop down lists



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 04:42 PM posted to microsoft.public.excel.worksheet.functions
MikeJ
external usenet poster
 
Posts: 10
Default multiple dependent drop down lists

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.
  #2  
Old April 14th, 2010, 06:11 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default multiple dependent drop down lists

Hi

Look at this site:

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

Regards,
Per

"Mikej" skrev i meddelelsen
...
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.


  #3  
Old April 14th, 2010, 08:23 PM posted to microsoft.public.excel.worksheet.functions
MikeJ
external usenet poster
 
Posts: 10
Default multiple dependent drop down lists

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.
  #4  
Old April 14th, 2010, 08:55 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
external usenet poster
 
Posts: 1,027
Default multiple dependent drop down lists

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&lookup var3,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.

  #5  
Old April 15th, 2010, 09:05 AM posted to microsoft.public.excel.worksheet.functions
MikeJ
external usenet poster
 
Posts: 10
Default multiple dependent drop down lists

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.
  #6  
Old April 15th, 2010, 12:00 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
external usenet poster
 
Posts: 1,027
Default multiple dependent drop down lists

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/datam...taexamples.htm


"Mikej" wrote:

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.

  #7  
Old April 15th, 2010, 02:11 PM posted to microsoft.public.excel.worksheet.functions
MikeJ
external usenet poster
 
Posts: 10
Default multiple dependent drop down lists

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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.