cascading dynamic unique drop down list
Hi,
I'm trying to implement a series of cascading/dynamic drop down lists (once var1 is selected, only unique relevent options should be displayed for var2, then var3 options should depend on var1&var2, etc....), which once all selected display data in the output section.
If I enter the input data manually the correct data is displayed. It is the dropdown lists I'm having problems with (using DGET for this).
I found this thread and tried to follow it by adapting it to my spreadsheet with not much success so far:
https://www.experts-exchange.com/questions/24467696/Cascading-Unique-Drop-Down-Lists-More-Than-Three-Columns.html#mainSection
I've attached a sample of my attempt with the integrated code taken from the link above. The data on "master" is close to what I'm really going to use (ie several spaces and illegal characters used). Each row on master sheet has a unique set of variables.
Thanks for the help.
form-example-16-04-2010.xls
I'm trying to implement a series of cascading/dynamic drop down lists (once var1 is selected, only unique relevent options should be displayed for var2, then var3 options should depend on var1&var2, etc....), which once all selected display data in the output section.
If I enter the input data manually the correct data is displayed. It is the dropdown lists I'm having problems with (using DGET for this).
I found this thread and tried to follow it by adapting it to my spreadsheet with not much success so far:
https://www.experts-exchange.com/questions/24467696/Cascading-Unique-Drop-Down-Lists-More-Than-Three-Columns.html#mainSection
I've attached a sample of my attempt with the integrated code taken from the link above. The data on "master" is close to what I'm really going to use (ie several spaces and illegal characters used). Each row on master sheet has a unique set of variables.
Thanks for the help.
form-example-16-04-2010.xls
ASKER
Hi,
You're right the data in each column is nearly random. There is no way to make them dependent as in the tutorial (I may have used the wrong terminology sorry). In theory I could end up with hundreds (or thousands) of unique combinations with the 5 variables.
You're right the data in each column is nearly random. There is no way to make them dependent as in the tutorial (I may have used the wrong terminology sorry). In theory I could end up with hundreds (or thousands) of unique combinations with the 5 variables.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Alain,
That seems to work in its current form, thanks !
Just a few questions for me to understand whether I can adapt it to my real worksheet.
1.Is it necessary to autofilter the master sheet in order to get the dropdown menus to work ? I'd like to be able to look at the entire raw data on the master sheet at anytime (either to look for data or to add/update rows). The filtering seems to really make my CPU work hard (it's a 4 year laptop, i know...) but I am conscious that some people using it may even have an older machine.
2. My final workbook will have 2 "input" sections with 2 "output" on the main sheet to allow comparaison of the data. Will anything stop me from duplicating it to work ? Apologies I should have created my sample form in this way. Let me know if I should re-work it to make it clearer.
Thanks.
That seems to work in its current form, thanks !
Just a few questions for me to understand whether I can adapt it to my real worksheet.
1.Is it necessary to autofilter the master sheet in order to get the dropdown menus to work ? I'd like to be able to look at the entire raw data on the master sheet at anytime (either to look for data or to add/update rows). The filtering seems to really make my CPU work hard (it's a 4 year laptop, i know...) but I am conscious that some people using it may even have an older machine.
2. My final workbook will have 2 "input" sections with 2 "output" on the main sheet to allow comparaison of the data. Will anything stop me from duplicating it to work ? Apologies I should have created my sample form in this way. Let me know if I should re-work it to make it clearer.
Thanks.
The autofilter is just temporary. You'll notice that it actually just copies the filtered cells to a third sheet and then restores the second sheet to it's normal state - no no worries there.
As for the filtering time, it may seem pretty steep now, but it doesn't scale much with data size, so you pretty much don't have to worry about it being any worse than it is.
There's no problem with having just 2 input sections. You'll notice that there's a few places where I reference the number of inputs there are. The can make less and still have it work about the same, you just need to adjust the code in minor ways. It'll also probably go much faster with just 2 inputs, since it's not filtering 5 times in a row.
--
Alain
As for the filtering time, it may seem pretty steep now, but it doesn't scale much with data size, so you pretty much don't have to worry about it being any worse than it is.
There's no problem with having just 2 input sections. You'll notice that there's a few places where I reference the number of inputs there are. The can make less and still have it work about the same, you just need to adjust the code in minor ways. It'll also probably go much faster with just 2 inputs, since it's not filtering 5 times in a row.
--
Alain
ASKER
What I meant by 2 inputs was actually 2x5 variables (will probably be 2x6 in my final workbook). I've modified the file you re-attached to reflect the double input/output. I am not sure now how to adapt it to make this work. Your expertise would be appreciated again. Thanks.
Auto-Populate-Drop-Boxes---dual-.xls
Auto-Populate-Drop-Boxes---dual-.xls
Hmm... I'm getting dental surgery today (skateboarding accident), so I might be out of commission for a little while. To ensure you get help as fast as possible, perhaps you should close this question and start a new one to give someone else the chance to jump in and adapt my workbook. It should be pretty easy for them to figure out.
Also post a link to the follow-up question in here so that I can check on it when I get over this is make sure it gets answered one way or another.
Thanks!
Alain
Also post a link to the follow-up question in here so that I can check on it when I get over this is make sure it gets answered one way or another.
Thanks!
Alain
ASKER
Oops...I will do thanks. Good luck with the dentist.
There is an excellent tutorial available online at contextures.com. Here is the link to that.
http://www.contextures.com/xlDataVal02.html
I was trying to apply that to your spreadsheet however it doesn't seem that you have unique values for var2, var3 and var4.
So I am a bit confused as to how do you want the lists to flow?
Say for example, you select Var1 as A, you want to show 2x AA and 2x BB in var2 list ? Can you please elaborate?
Thanks,
Ardhendu