0

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.
enter image description here

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
enter image description here

Improve this question
  • 2
    You need Dependable Drop Down method for Validation ! – Rajesh S Mar 16 '19 at 8:38
  • @RajeshS i understand that part, im having problem implementing it as the examples i have seen online require the list sheet to have separate tables for Group and Names. I want them to be in the same table as i want to use VlOOKUP later. – John Mar 16 '19 at 10:45
  • If you could have each group in a separate table, you could do like in the following example: superuser.com/questions/1137213/… but maybe that's not an option. – Christofer Weber Mar 16 '19 at 12:55
  • It would be helpful if you could explain what you are trying to achieve. What is supposed to happen once the user selects name and group? If names are unique, why both selecting a group, since (presumably) only one group will be associated with any given name. – cybernetic.nomad Mar 16 '19 at 13:42
  • @ChristoferWeber i'm trying to keep the table as one and not separate if that is at all possible – John Mar 16 '19 at 14:06
1

enter image description here

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 image description here

  • 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.

enter image description here

  • 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.

Improve this answer
  • Thank you so much for your time, this does the job. I had never known about array formulas before. I learnt something new today which is always a plus! – John Mar 17 '19 at 12:31
  • @John,, glad to help you ,, keep asking ☺ – Rajesh S Mar 18 '19 at 4:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.