Is this considered a dynamic dependent drop-down? AND how can I make this work?

[Livingwil]

I'm not sure if this is considered a "dynamic dependent drop-down list" or conditional or v-lookup - and I cant seem to figure out the correct solution for my needs...:

1) I have Interview competency categories, each with its OWN SET of questions based on the competencies selected...
2) on separate worksheet, in cells A4-A10 I want to have a manager select 5-6 competencies which would then give them the option in B4-B10 to choose from a drop-down list questions based on the competency selected.

Ex: In cell A4, manager selects competency "Building Trust" from drop-down. B4 would then auto-populate a drop-down with only the questions for "Building Trust"
Please note that some competencies have more questions than others...

I really want to learn what this is and how to make it work!

[FDibbins]

Hi, welcome to the forum

Your sample doesnt really match your question

However, attached is a dynamic drop-down. It uses named ranges for each "set" (and sub-set, and sub-sub-set) of data, and then uses INDIRECT() to show the DD list.

Take a look through it and see if you can adapt it for your use?

[daffodil11]

I prefer to use tables.

Make a list of things for the first menu, and make a list next to it of table names. We do this because we like pretty outputs but table names need underscores. We use this to convert inbetween.

Next, each list of other outputs is its own table. Select the range of selectable answers, and define that range as one of the converted Competencies.

For example, option 1: Aligning Performance for Success, which translates to Aligning_Performance_for_Success.
If you select E2:E10 in my example you'll see that range is also named "Aligning_Performance_for_Success"

Dropdown 1 is set to list, C1:C5.

Dropdown2 is set to list, =INDIRECT(VLOOKUP($A$1,$C$1:$D$5,2,0))

This will translate the option 1 to a real table name, and pull its list of relevant values.

[oeldere]

See the attached file.

I made this with a defined name (see red text in the sheet) and datavalidation (see red text in the sheet).

[FDibbins]

I have updared my file so teh categories/sets are easier to follow

[Livingwil]

Thank you! Seeing the different formats you provided is very helpful. I am testing the options now.

[Livingwil]

Thanks again for your assistance with this! I was able to replicate everything except for one small thing...
How did you keep the "Select Competency" and "Select Question" visible?

[daffodil11]

I made them the top choice in each dropdown reference table. Because I'm crafty.

[Livingwil]

Yes you are!
Thank you

[daffodil11]

No problem!

Come back if it keeps giving you trouble. We'll get through this.

[Livingwil]

I thought I had it...
What am I missing in the Dropdown 2 formula?
I created 5 additional rows in columns A & B - each duplicating the formulas you provided.
Regardless of the competency selected in column A, Column B's dropdown options are for "Aligning Performance for Success"

[daffodil11]

Each formula for a given B will have to reference the given A.

So if you have a cell in A1, and a contingent in B you'll use =INDIRECT(VLOOKUP($A$1,$C$1:$D$5,2,0))


If you start in A50, your contingent will use =INDIRECT(VLOOKUP($A$50,$C$1:$D$5,2,0))

Form building tends to have a lot of non-replicateable steps, where you have to specify for each.

[Livingwil]

You are the man!! It makes perfect sense and worked great!
Thanks again!

[FDibbins]

Thanks for the feedback