Dependent Drop-down List

Question

Hi,

I'm trying to create a 'dependent' drop down list where there will only ever be one option for it.  I'm not very good with excel (no training) so would really very much appreciate all the help I can get to figure out how to do this!

Goal:

A Main Drop Down list gives you the option to select a tv/film character e.g. (Sansa Stark).  Once the character is selected, you click the dependent drop down list and the only option it gives you is 'House Stark'.

MAIN DROP DOWN LIST: Character Name

DEPENDENT DROP DOWN LIST: Family Name(s)

Other Examples: Main Drop Down is to select the name of a character, Dependent Drop Down forces your choice to a set 'fact', such as: (i) the right television show (e.g. GoT); (ii) dead/alive; or (ii) name of the relevant actor/actress.  I'm not looking to do all of that, just trying to make it super clear that the first choice forces your hand to a singular choice that may/may not apply to others that are selected in the Main Drop Down.  

 What I've done so far:

• Created a table in a new 'List Tab', the table is named (e.g. CharacterTable).
• The table constitutes two columns ('Character Name' and 'Family Name(s)'). The information has been completely filled out in both (over 200 rows).
• Each 'column' ('Character Name' and 'Family Name(s)' has got the 'secondary name' (apologies, not familiar with lingo, next to the "X TICK FX" buttons, which itself is next to where you input formulas) from me highlighting the relevant column information (excluding the header)) and named them as 'CharacterName' and 'FamilyNames' respectively.
• On the Main Tab/Table, the column where the Character Names will go (minus the header) have been formatted with 'Data Validation' as follows:
  • Allow: List (ignore blank, in-cell dropdown).
  • Source: "=CharacterName".
  Conclusion: Current position is the 'main' drop down list is set up, it's figuring out how to do the singular dependent drop down list that I really need help with.

Key points/problems:

1. Both the 'main' drop down list & the dependent drop down list contains more than one word (2-7 with punctuation (e.g. &)
2. The 'main' drop down list has over 200 options to choose from.
3. As mentioned above, there is only ever one option for the dependent drop down list.
4. The overarching document/table will be updated with new rows added/deleted regularly and will be used by people who won't know/be updating any formulas (which is why I don't think we want to use 'VLOOKUP' (correct me if I'm wrong) if that means that someone has to 'remember' to copy paste in a formula when they add new rows in.  They won't do that (or remember to do that) and it would render all this work meaningless.  For that reason, I think a dependent drop down list is better as it seems to carry the formatting when you add new rows.

Apologies for the length - thank you very much for any help you're able to give!

Answer 1

Hi Rebecca

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

I need to have a look on your workbook to provide you the solution specific to your problem.

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and right solution.

A guide to upload and share Onedrive file - https://support.office.com/en-us/article/share-...

Do let me know if you require any further help on this. Will be glad to help you.