Forum Discussion

Badoumba's avatar
Badoumba
Copper Contributor
Jan 21, 2024

Structured Tables, Names and dynamic list

Hi everyone,
New to Excel I try to achieve the following:

Table_c

Depending on the name selected ([Name]), I'd like to list possible actions ([Actions]) from table_b only for the type Grain (table_a), empty if it is Fruit.


I tried the following but can't solve the formulas errors:

1 - Retrieve corresponding type : works well with:

=INDEX(table_a[Type];MATCH(table_c[@Name];table_a[Name];0))

when typing this as a new column of table_c or side to it ith the rows corresponding, but I cannot convert it to a Named function (Name Manager) which would return is_of_type_grain as TRUE or FALSE. Seems like @ symbol does not work.
2 - I cannot find how to add a IF statement for a list based on the value of another cell from the table or using my named function directly which refers to this table cell.

All I find in Google is not directly related to these intricated issues.
Thanks for any help.

Badoumba

excel
  • djclements's avatar
    djclements
    Bronze Contributor
    Jan 21, 2024

    Badoumba Does the attached workbook do what you are trying to achieve? Data Validation does not accept structured table references, so you have to define a name for each column that you want to reference. For example, I defined names for lstNames and lstTypes to be used in the INDEX / MATCH lookup, as well as Grain and Fruit to be returned by the INDIRECT function as follows:

     

    =INDIRECT(INDEX(lstTypes; MATCH($D3; lstNames; 0)))

     

    I'm not exactly sure if this is what you were going for. Check it out and see...

    • Badoumba's avatar
      Badoumba
      Copper Contributor
      Jan 21, 2024
      Hi djclements
      Yes, perfectly! A big thank you for the tip!.
      Just for the sake of understanding, can $D3 be replaced by a name also? I tried with lstName_c (=table_c[Name]) but it is not working.
      • djclements's avatar
        djclements
        Bronze Contributor
        Jan 21, 2024

        Badoumba Include the "@" symbol to indicate "This Row". Also, be sure to include the table name in the reference. For example, lstName_c: =table_c[@Name].

         

        Having said that, the whole thing can be simplified by defining a name called lstActions with the following formula:

         

        =INDIRECT(INDEX(table_a[Type]; MATCH(table_c[@Name]; table_a[Name]; 0)))

         

        Then just use =lstActions as the Data Validation list source (see attached). Cheers!

Resources