Answer to Forum question

Vishesh's picture

Refer attached Excel file for answer to forum question on
http://excelexperts.com/creating-2nd-list-dependent-1st-list

See data validation criteria conditions on Cell A2 and B2. Also see Named ranges created (dynamic named ranges can also be used here)

AttachmentSize
Dependent changing list.xls24 KB

Dependent changing list

Thank you that helped you have the right idea. but I can see the formula of how you got there and I think I need to know how to change or add to the named range. I can only use the first 3 lines you included. Anything I add does not show up.

Thank you

Almir's picture

Use INDIRECT IN B2, and here is how to create named ranges

To create named ranges:
1. Select the table including column names,
2. On tab "Formulas" choose "Create from Selection" and select "Top Rows"
3. In B2 Data Validation rules choose "List" and type: =INDIRECT(A2). Thus, you will not have to change IF formula in Data Validation box whenever you add or change items in your table.
1. In order to avoid re-creating named ranges each time you add new rows to your table, convert your table to Data Table:
1. Click in a single cell anywhere in the table
2. Press CTRL+T
3. Select "My table has headers"
4. Press "OK" button

Almir's picture

Use INDIRECT IN B2, and here is how to create named ranges

To create named ranges:
1. Select the table including column names,
2. On tab "Formulas" choose "Create from Selection" and select "Top Rows"
3. In B2 Data Validation rules choose "List" and type: =INDIRECT(A2). Thus, you will not have to change IF formula in Data Validation box whenever you add or change items in your table.
1. In order to avoid re-creating named ranges each time you add new rows to your table, convert your table to Data Table:
1. Click in a single cell anywhere in the table
2. Press CTRL+T
3. Select "My table has headers"
4. Press "OK" button