3. Data validation using “Name Manager”



 PROBLEM:  How to create a dropdown list in column "Description" according to column "Source" column.





  1.  First, build an aux table, from your main table, with 2 columns, "Source" and "Description". To make it easy to understand, in my video, I did it in the same sheet;

  2.  Extract from previous table and column Description" the corresponding values from the column "Source" using the formula:

    =IFERROR(FILTER(TableXX[Description];TableXX[Source]=G32);"").

    TableXX is your table. "G32" is a cell where your value is and you want to include. Repeat for how many values you want.

  3.  Select, in the main table, the column "Source". Then, go to tab "Data" and select "Data validation". Hit "Allow" and select "List". Finally, in "source", select "G32"; "G33", and so on;

  4.  Go to tab "Formulas and select "Name Manager". Hit "New" and name it, for example, "SPOT". In "Refers to" insert:

    =INDEX(('EXCEL - NOT AN EXPERT'!$G$33#;'EXCEL - NOT AN EXPERT'!$H$33#;'EXCEL - NOT AN EXPERT'!$I$33#;'EXCEL - NOT AN EXPERT'!$J$33#;'EXCEL - NOT AN EXPERT'!$K$33#;'EXCEL - NOT AN EXPERT'!$L$33#;'EXCEL - NOT AN EXPERT'!$M$33#);;;MATCH('EXCEL - NOT AN EXPERT'!$M22;'EXCEL - NOT AN EXPERT'!$G$32:$M$32;0))

    This formula, will extract the "Description" values returned in step 2, matching each cell from column "Source" (main table), as long as they are equal to "G32"; "G33", and so on;

  5.  Select, in the main table, the column "Description". Then, go to tab "Data" and select "Data validation". Hit "Allow" and select "List". Finally, in "source", writedown "SPOT";



    6. Hard to understand? See video, maybe it will help.