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.
0
Utf
4/14/2010 3:42:09 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
3394 Views

Similar Articles

[PageSpeed] 32

Hi

Look at this site:

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

Regards,
Per

"Mikej" <Mikej@discussions.microsoft.com> skrev i meddelelsen 
news:5005BD96-E2DB-429E-A882-C892557261FE@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. 

0
Per
4/14/2010 5:11:18 PM
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.
0
Utf
4/14/2010 7:23:02 PM
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.
0
Utf
4/14/2010 7:55:01 PM
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.
0
Utf
4/15/2010 8:05:01 AM
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" 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.
0
Utf
4/15/2010 11:00:01 AM
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.
0
Utf
4/15/2010 1:11:01 PM
Reply:

Similar Artilces:

Multiple Worksheet Scrolling
This is the first time I have ever posted on a forum so please forgiv me if I do something wrong. I am creating a workbook with multiple spreadsheets, the first shee simply totals cells in the other sheets. All sheets are very simila and have information all in the same layout. I would like to get al of the sheets to scroll together at the same time so if a user switche worksheets then they will be in the same location as on the previou worksheet. This would greatly enhance the usability of this workbook. I don't know how to explain this any better right now, if I was unclea on somet...

Cannot print both-sided by selecting multiple worksheets
Any batch or 3rd-party program can fix this problem?? When printing multiple worksheets, it print one sheet as one file... I must select "page from X to X" to resolve this problem every-time. It's really trouble to do this, and as I remember it works before upgrade XP SP3. ...

Contact list question
I now have my contacts listed online with a Windows Live ID. But, I notice the display in WLM is different ... depending on if I am logged into Windows Live ID or not. When I am not logged in, I see all my Groups (or Categories) but when I am logged in, I see none. The total contacts is the same. Is there any way to duplicate the Group/Categories when online? Tom You are looking at two independent contact lists. Any changes made while logged in will not be reflected when not logged in and vice versa. You would have to manually recreate the groups while logged in. ...

Distribution List #3
I have Outlook 2002 - I want to set up a distribution list but do not want each person on the list to view each other email address - Just want the TO: line to be generic (e.g. EVERYONE) Thanks, Bob Hi Bob, In order to hide e-mail addresses from other recipients, type your address in the To field and place the distribution list in the Bcc field of the e-mail message. For more information on , please refer to Knowledge Base article ID: 257599.KB.EN-US, OL2000: How to Hide E-mail Addresses from Other Recipients You can access this article by clicking on the link below. http://support.m...

multiple empty accounts showed up
I have three accounts with Broker X, which I've tracked for some time with Money (2006 Premium). Only one of them was set up for automatic updates. As soon as I enabled the other two for automatic updates, Money created SEVEN new empty accounts ("Broker X Cash Account", "Broker X Cash Account #2", ... "Broker X Margin Account", etc.) separate from the two I wanted to connect. Next thing I know, I have NINE pending statements for these seven imaginary and empty accounts, displaying "bank balance" totalling somewhere in the range of $200,000! Go...

dropped recipients in e.mails
Hi, Exch 5.5 SP4 on NT4 SP6a Recently, e.mails that are sent to multiple recipients will not reach 2 or more of the internal users on the distribution list for e.mails from external parties. Per someone's suggestion, message tracking was enabled. Before the logs are posted here in an ugly haphazard fashion, here's some background. The "cn=johnyoo1124" is there because it's a custom recipient created for a one-time mass e.mail sent to clients. That cn= is the user who sent the e.mail. I forgot to delete the custom recipients after the mass e.mail was sent on Monday...

Which one of the below list is UNICODE charset?
Which one of the below list is UNICODE charset? from msdn: ================================================================ lfCharSet Specifies the character set. The following values are predefined. ANSI_CHARSET BALTIC_CHARSET CHINESEBIG5_CHARSET DEFAULT_CHARSET EASTEUROPE_CHARSET GB2312_CHARSET GREEK_CHARSET HANGUL_CHARSET MAC_CHARSET OEM_CHARSET RUSSIAN_CHARSET SHIFTJIS_CHARSET SYMBOL_CHARSET TURKISH_CHARSET VIETNAMESE_CHARSET Korean language edition of Windows: JOHAB_CHARSET Middle East language edition of Windows: ARABIC_CHARSET HEBREW_CHARSET Thai language edition of Window...

553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) #2
I keep having this message when I send emails. The pop and smtp are different. Thank for your help 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) Are you set up to authenticate to your SMTP server, if your ISP requires it? isaac wrote: > I keep having this message when I send emails. The pop and > smtp are different. > Thank for your help > 553 sorry, that domain isn't in my list of allowed > rcpthosts (#5.7.1) ...

Not able to remove the indentation in the list control after removing the imagelist
Hi, Anyone can help me. In my application once I remove the imagelist from the list control, indentation or space exists in the list control. I tried with setting iImage and iIndent members of LVITEM structure to -1. But no luck. Thanks in Advance, Balu. You could try calling Detach() for the image list in the CListCtrl. For example: CListCtrl *pMyList; // Added image list using SetImageList() pMyList->GetImageList()->Detach(); http://msdn2.microsoft.com/en-us/library/b75edw6k.aspx Tom "balu" <balakrishnakk@gmail.com> wrote in message news:1158207742.071489...

Public Folders not listed on Folder List
I cannot see Public Folders on my Outlook 2003 SP2 Folder List. I am using Exchange 5.5 SP4. Please help. Have you permission to see each folder? /Lasse "M P" <mark@textguru.ph> wrote in message news:ORUUCoMpGHA.3584@TK2MSFTNGP03.phx.gbl... >I cannot see Public Folders on my Outlook 2003 SP2 Folder List. I am using >Exchange 5.5 SP4. Please help. > Could you see them yesterday? Describe what happened - what changes did you make to the system? .. -- Judy Gleeson Microsoft Most Valuable Professional Outlook www.acorntraining.com.au "Lasse Pette...

create a list of email addresses
I have about 50 individual emails and would like to make a list of those email addresses to send out other emails to those people. Is there anyway that outlook can create an excel sheet or group with a command, instead of dealing with each individual email. Does this make sense? Outlook 2003 Windows XP Thank You Meg "********Meg" <alumni(removethis)@swedishinstitute.edu> wrote in message news:uObgCQClIHA.2368@TK2MSFTNGP03.phx.gbl... >I have about 50 individual emails and would like to make a list of those >email addresses to send out other emails to those peopl...

Why is Bank of America CA not on list of bank in new Microsoft Money Plus Home and Business
Hi, I see Bank of America (All except CA, WA, and ID) and then Bank of America (WA and ID). Am I missing something or am I interpreting this incorrectly? In microsoft.public.money, ado510@gmail.com wrote: >Hi, I see Bank of America (All except CA, WA, and ID) and then Bank of >America (WA and ID). Am I missing something or am I interpreting this >incorrectly? There have been some recent changes in the Bank of America statement download area: From http://www.microsoft.com/money/bankonline.aspx?FirstLetter=B&FinananceOrganisationType=BanksOnly#14 there are 3 choices now show...

VBA To Deliver Object List?
Can I write a single VBA loop to enumerate all objects within an Excel .XLS? Or do I have to know the object types/container names and enumerate the contents of each? -- PeteCresswell see: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=list+objects&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=cb2f7677-be8c-40b0-bfa9-2305acd4d167 -- Gary's Student "(PeteCresswell)" wrote: > Can I write a single VBA loop to enumerate all objects within an Excel .XLS? > > Or do I have to know...

WLM, Windows Live Contacts, and Multiple Accounts
I have 2 Hotmail accounts configuring in WLM Desktop. When I select Go==>Contacts, the Windows Live Contacts dialog is shown, but only for _one_ of my email accounts. I can't figure out how to show the contacts related to the other account. Does anyone know how? Thanks, P. Upper right corner where your live acct ID is shown.. "sign in with a different ID". Choose other ID. "Oceanclub" <Oceanclub@discussions.microsoft.com> wrote in message news:95D4DAD4-3307-4FA7-8BE6-A73C4DE198D4@microsoft.com... > I have 2 Hotmail accounts configuring ...

exporting list
Hallo, I have a little problem i'm trying to solve. I would like to know the way to export a list of all e-mail adresses from mij exchange 5.5 server. somebody told me that there was a way to make a report using crystal reports but i have been trying this also without succes. if anybody kan helpme whith this please let me know. Thanks http://support.microsoft.com/default.aspx?scid=kb;en-us;278154&Product=ech http://support.microsoft.com/default.aspx?scid=kb;en-us;155414&Product=ech Martin Salazar wrote: > Hallo, > I have a little problem i'm trying to solve. > I ...

Multiple reports within one report
I want to create a single report so the pagination (header and footer info would be consistent).... BUT, this report will be composed of a series of small table-style reports, each accompanied by a pie chart. My problem is that the client wants the pie charts NEXT to the tabular report. So, I have a subreport on the left, which can be set to grow or shring, and a pie chart on the right, which appears to be limited to whatever size I make it. Beneath each report will be a section of "free-form" comments pertaining to the report. The problem is that I have approximately 8 of these...

Multiple E-mail Address for 1 User with Forwarding
Is it possible to create 2 E-mail addresses for one user and have the 2nd forward to the first? TIA. -- Regards, Allan C They are currently running Exchange Server 5.5 sp4. The scenario is that one user is receiving a lot of Spam. He would like to change his E-mail address * now * before he can contact everyone. Even if he did, some people will send to his old address by mistake. So, we would like to have 2 addresses running concurrently so nothing drops in the bit bucket. We would like to have the first address (with the Spam) forward to the 2nd so that he doesn't have to login ...

POP Invoive Edit List not coming out for 1 transaction
Hi, We are trying to print the edit list in the purchasing invoice entries but there is no output for 1 particular entry. We dont have issue in other entries. The data are correct and I can see that the system is printing in the Process Monitoring but no output in screen, printer and even in file. What can be the problem. Kindly help, thank you. Since this is unposted why not delete this entry and auto invoice again. The original PO Invoice details may have been corrupted. Just keeping the solution simple. Cheers "Bunxie" wrote: > Hi, > > We are trying to p...

Multiple lines from different tables in a report detail
I'm not sure if I can easily describe what I'm trying to do here, but here goes... Let's say I'm trying to make a report that describes the vehicles each person in my database owns. So I have a table for each person, a table for cars, and a table for pickup trucks. There's a one to many relationship from each person to the car table, and from person to trucks. The car and truck tables don't have the same fields, so I need them to be different tables. I want my report to look like this: Person Car Name Car Mileage Truck Name Cargo Cap...

Data structure for sorted list
I'm looking for ideas on how to create a sorted list that executes in the shortest possible time. Facts about the app: * Presently, it builds a vector from external data, then quicksorts it * Each data node has several (potentially long) strings to sort on, which makes makes comparisons quite slow * The number of nodes can vary from several dozen to tens of thousands. * It's compiled with VC6, though if needed, an upgrade to 2008 would be considered Would a binary tree be the way to go? stl::map? Would another structure execute faster than a simple quicksort? Any thoughts wou...

why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Dim sheetcnt As Integer Private Sub cmdImport_Click() sheetcnt = 1 Daniel, Dimming a variable outside the procs makes it module-level. If you'll need to use it across modules, make it project level: Public sheetcnt As Integer -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:%23ya7IdBhFHA.2916@TK2MSFTNGP14.phx.gbl... > w...

Where can we get a complete list of VC++ data types?
Hi, We need a list of all VC++ Data Types. Searched through MSDN but could not find a single list. Since we are new to VCC+ we really don't know if what we are getting is sufficient or there is more. Would help if we could get some reference link. Thanks and Regards, M Shetty news:1159884090.972166.178530@e3g2000cwe.googlegroups.com...> Hi, > > We need a list of all VC++ Data Types. Searched through MSDN but could > not find a single list. Since we are new to VCC+ we really don't know > if what we are getting is sufficient or there is more. It's not clear that ...

Payroll Transaction Edit List
I would like to modify the Payroll Transaction Edit list to have a pay total per employee and an overall sum at the bottom of the report. I have tried to modify it by multiplying the pay rate by the amount which is in hours. This works for hourly employees, but does not for salary employees. The salary employees pay code is setup with their annual salary so it is multiplying their salary by the hours which is totally wrong. How can I get it to calculate correctly for both hourly and salary employees? Thanks, Jocelyn A calculated field based upon the pay code or pay type would prob...

How do I transpose multiple rows into columns?
I have data organized (in Office 2007) as such: A B C D E F and so on I want to tranpose it so it appears in columns as: A B C D E F and so on. Is there a way to transpose multiple rows so the information is stacked into columns? Thanks a million! Copy > PasteSpecial > Transpose.........as many times as you need......can be set to a macro if done frequently.. Vaya con Dios, Chuck, CABGx3 "Nick" wrote: > I have data organized (in Office 2007) as such: > > A > B > C > > D > E > F > > and so on > > I want to tranpose it so...

outlook multiple e mail users
how do I set up outlook so that each of our outlook e-mail addreses require passwords to access? as soon as I click on oulook, my e-mail pops up. no password casper wrote: > how do I set up outlook so that each of our outlook e-mail addreses require > passwords to access? as soon as I click on oulook, my e-mail pops up. no > password Go into the account defined in Outlook and blank out the password field. Also uncheck the "remember password" checkbox. You can't. You'll need to set up multiple user accounts on the computer if you want password protection. -Pe...