Forum Discussion
Dependent Drop Down Lists
Amazing thanks! I can’t see the formulas you’ve used, please can you tell me how to achieve this?
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