Answer to Forum question
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)
| Attachment | Size |
|---|---|
| Dependent changing list.xls | 24 KB |
»
- Vishesh's blog
- Login or register to post comments
- 7130 reads
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
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
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