microsoft excel - Using data validation to list a column

I have a workbook with a table that has headers with names and group. The names column is distinct (no name is repeated) whilst the group column has values (like G1, G2, G3 etc) which are repeated. I want to create 2 lists, the first one of which will allow the user to select a group. The second list will then allow him to select a name from that group. Is this possible? I have been trying to get a solution that works but it seems all of them require that the row values in my group column need to be changed into headers

Edit: More details Not able to post images due to rep. Here is a link to the setup The first image shows the data sheet with a table with names, group and ID.

On the user sheet i want to add a data validation list for both group and names. The user selects a group like G2, then under names he gets a list of the people who are in G2 and can select whomever he wants

Answer

Note: Since your Ids and Names are resembling to Cell/Row references, therefore instead of standard method, I would like to suggest use an Array Formula to create Dependant Drop Down List.

How it works:

• Select Cell E72 & From DATA Tab click Data Validation then hit Settings and fill values as shown in Screen shot below & finish with Ok.

• Enter this Array (CSE) Formula in cell A83, finish with Ctrl+Shift+Enter and fill down.

{=IFERROR(INDEX($A$72:$A$80,SMALL(IF($C$72:$C$80=$E$72,ROW($A$72:$A$80)-MIN(ROW($A$72:$A$80))+1),ROWS($A$83:A83))),"")}

• 
  

  Enter this Array (CSE) Formula in cell C83, and finish with Ctrl+Shift+Enter.

  
  

  {=IFERROR(INDEX($B$72:$B$80,SMALL(IF($A$72:$A$80=$G$72,ROW($B$72:$B$80)-MIN(ROW($B$72:$B$80))+1),ROWS($C$83:C83))),"")}

  
  


• 
  

  Select cell G72 reach to Data Validation, click Settings and fill values as shown in Screen Shot below.

  
  

• Finally select cell I72 reach to Data Validation, click Settings and fill $C$83:$C$83 for Source then finish with Ok.

N.B.

• Now, start clicking Group from 1st Drop Down then you find related Names in 2nd Drop Down, click any, finally you find Id in 3rd Drop Down.


• Later on you may hide A82:C85.

Adjust Cell references in the Formula as needed.