Dynamic Dropdown List Capability based on "options"

SiddharthRout has helped me write a great routine selection using drop down boxes to select the correct choice and then subset the questions.  However, in order to actually implement this, I need help with a problem with the dropdown box.  I need a little code in the dropdown macro that only allows the choices in either Category or Type to be seen if the "combination" exists….either way.  In other words, if you select “C” in Column C, then the only choice you would have is “1”.  If you select Type “3” then only “A” and “B” will show in the dropdown.

Good luck!  And “Thanks in advance…….”

Dynamic-Table-Lookupv2.xls
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
Is this what you want? Sample File Attached.

Sid


Dynamic-Table-Lookupv2.xls
Bright01Author Commented:
Man you are good.

I've looked over the code...... not sure I understand it all.  Here's my question.  I'm integrating over 400 questions (probably 12 to 15 Categories and Types).  When I used the last version, I got the same problem I had earlier....... which was the questions ended up way down the column.  Is there a simple fix?  I'll integrate this new code and let you know if I have the same problem.

Much thanks!

B.
SiddharthRoutCommented:
Ignore the earlier files. I don't think you should have the same problem with the above file. :)

Sid
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Bright01Author Commented:
OK....try this..  Delete both Cat. and Type.  Now put in Type first.  You will see.... you don't get all of the Cat. options.  And you will see the problem with the questions moving to the "top".

Quick feedback.

B.
Bright01Author Commented:
Sorry.... put in Cat. first.
SiddharthRoutCommented:
When you delete Cat. and Type, do you get any error?

Sid
SiddharthRoutCommented:
Try this file.

Sid
Dynamic-Table-Lookupv2.xls
Bright01Author Commented:
Sid,

This looks good.  However, we need to put in a "clear" function that allows to start over. The reason for this is that if you go through the normal path, and then delete both Type and Category, you don't have all of your choices.

Help!

B.
Bright01Author Commented:
If you want to try it..... run through a sequence....then delete both input variables.  Then go to Category and see what drops down.

B.
SiddharthRoutCommented:
Like this? :)

Sid
Dynamic-Table-Lookupv2.xls
Bright01Author Commented:
Sid,

This version doesn't work at all.  Please test it.  If I select a Category or Type, I don't get any questions.  What am I doing wrong?

Sorry,

B.
SiddharthRoutCommented:
I just tried this file and it works. See the Screencast.

Sid
Dynamic-Table-Lookupv2.xls
SiddharthRout-439854.flv
Bright01Author Commented:
Sid,

Thanks!  And a movie to go along with it.  I'm calling out for pizza and beer.  

So try this.  I've attached another version for you to look at.  I attempted to increase the "clearcontents" from i17 to i200 but when retesting..... it didn't clear it.  So I put it back so as to not disturb your code.

Please clear it and "populate".  Then select Category first, say "2".  Then select "C" for the Type. You will see the Questions appear below the yellow box (color of box not important).    

Is there a way to not have to "populate each time you add a row?


Thank you,

B.
Dynamic-Table-Lookupv7.xls
SiddharthRoutCommented:
>>>Please clear it and "populate".  Then select Category first, say "2".  Then select "C" for the Type. You will see the Questions appear below the yellow box (color of box not important).    

Taken care of.

>>>Is there a way to not have to "populate each time you add a row?
Yes but it would be too messy.

Sid
Dynamic-Table-Lookupv7.xls
Bright01Author Commented:
Got it. I'll live with the "Populate" button.  One issue with testing;

Pull up your latest version.  Put "4" in the Category and then move to the Type.  Only "F" should be visable.  It works if you go from Type to Category, but not the other way.  Hopefully a simple fix.

Thank you!

B.
SiddharthRoutCommented:
Try this. I also moved the buttons and moved the question list to the top so that you can have more space to see the questions.

Sid Dynamic-Table-Lookupv7.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Sid,

Thank you and it continues to get better.  I just populated it with 50 Cases and it worked well.  I've got another 350 to put in and see if it holds up.  What considerations do I need to make if I plan to store the data on a different sheet or move the input (Type/Cat.) and display (Questions) to another part of the sheet (such as in Col. A, B and C?

Thanks again.

B.
SiddharthRoutCommented:
>>>>What considerations do I need to make if I plan to store the data on a different sheet or move the input (Type/Cat.) and display (Questions) to another part of the sheet (such as in Col. A, B and C?

Set up relevant references and it should be good to go :)

Sid
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.