Data Validation Dependent On Group Name

Question

Hello All,

I hope I can relay my question accurately. 

I want to have a cell dependent on the group name by selecting a name I choose that's in a list. For an example:

Group A:

Matt

David

June

Group B:

Jessica

Tom

Daisy

On one cell, I want to have a list that displays both Group A and Group B names. If I were to choose a name that's in Group B, the cell next to it will have only a list of Group B. 

List: (Group A + Group B) > Select Tom > List: (Group B) > Select Daisy

Tom (cell that displays both Group A & Group B)	Daisy (cell dependent of the left cell, only showing names of Group B)

I hope this make sense. I tried using the INDIRECT function, but the tutorials I found only demonstrates selecting a group, and then a list within the group (ie Fruits>apples). 

Thank you!

Answer 1

Excel 2010/2013/2016 Power Query (aka Get & Transform)

Dependent Data Validation.

No Indirect()

http://www.mediafire.com/file/8vmwh81258nsoej/02_04_17b.xlsx

Answer 2

Suggest you have a look at Debra Dalgleish's Dependent Lists DV

See here and note the Example workbook download.

http://www.contextures.on.ca/xlDataVal02.html

Gord