Forum Discussion

Deleted's avatar
Deleted
Mar 27, 2018

Dependent Drop Down Lists

Hey - please can someone advice me of the best way to create a database in the following way    I would like to use three drop down lists, each dependent on the one before it.    The first dr...
Formulas & Functions
tips and tricks
Deleted's avatar
Deleted
Mar 27, 2018

Thanks so much for this - the Excel tables with dynamic range feature is just what I'm looking for. 

 

Do you know if it's possible to hide results until selections have been made?

 

E.g.

 

1) In the first drop down list Select 'Role 1'

2) In the second drop down list select 'Person 1' 

3) In the third drop down list select 'Email' 

4) Only result displayed is the email address of Person 1

 

Thanks again

 

Jamil's avatar
Jamil
Bronze Contributor
Mar 27, 2018

Please see attached.

 

with your additional criteria you need three dropdowns and one cell to return the details based on the three selections of dropdowns, if the dropdown data is selected incorrectly then it will show a text in that yellow cell.

 

  • Deleted's avatar
    Deleted
    Mar 28, 2018

    Amazing thanks! I can’t see the formulas you’ve used, please can you tell me how to achieve this?

    • Jamil's avatar
      Jamil
      Bronze Contributor
      Mar 28, 2018

      You are welcome.

       

      The important formulas are placed inside the Data-Validation and are the following formulas.

       

      The reason to use INDIRECT in D2 and G2 is, because when referring to Excel Table Inside the Data Validation, unlike traditional named range, Excel Table Structural references require to be wrapped inside INDIRECT("TableRef") otherwise DataValidation will not accept that as a valid reference.

       

      Excel 2016 (Windows) 64 bit

        A B
      7 Address Formula1
      8 A2 =INDIRECT("tblCategory[Category]")
      9 D2 =OFFSET(INDIRECT("tblType[#Headers]"),MATCH(A2,INDIRECT("tblType[Category]"),0),1,COUNTIF(INDIRECT("tblType[Category]"),A2),1)
      10 G2 =OFFSET(INDIRECT("tblType2[#Headers]"),MATCH(D2,INDIRECT("tblType2[Category]"),0),2,COUNTIF(INDIRECT("tblType2[Category]"),D2),1)

      Dropdowns



      and the formulas used inside cells are the below formulas.   only formula in H2 is important, the other hyperlink formulas are not important. those three hyperlink formulas are used just to change mouse cursor to Hand.  

      Excel 2016 (Windows) 64 bit

        A B
      7 Address Formula
      8 B2 =HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
      9 E2 =HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
      10 H2 =HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
      11 J2 =IFERROR(LOOKUP(2,1/(tblType2[Main Cat]=$A$2)/(tblType2[Category]=$D$2)/(tblType2[Brand]=$G$2),tblType2[Column1]),"Error Data Does not exist in the table")

      Dropdowns



Resources