To create dynamic dropdown list in excel 2013

Question

I have one sheet containing the table name customer_id and Project_id.For each customer id various projects are displayed.when i try to create data validation by selecting all those it is not coming.for ex. Customer_id Project_id 1 1.1 1 1.2 2 2.2 2 2.3 and like that.please tell me some solution.

Answer 1

This really depends on what you want exactly, but since your question is not that elaborate, I guess you mean this?

You can create a data validator from data in your worksheet in the following way:

• Select the sheet you want the validator to be on
• In the ribbon, click the data tab, and then Data validation
• Select 'Allow List'
• in the Source bar, select the cells with the allowed values

Another way, is to create a data validator using a vba or vb.net macro. In your macro say:

Dim range as Excel.Range = listSheet.Range("A1")
range.Validation.Add(Excel.xlDVType.xlValidateList, Excel.xlDVAlertStyle.xlValidAlertInformation, Excel.xlFormatConditionOperator.xlEqual, "Option1; Option2")

Answer 2

I guess what you want are two dropdown lists, the second depending on the value selected in the first one? (please elaborate your questions some more). To do that, I suppose there are other ways but this is what I usually do:

Take into account that I always use names to refer to ranges instead of references, to improve readness. If you have doubts to do it, read Use names in Excel formuas

1. Create a table or range somewhere in your book with one column with the master values.
   • I have named the range with the values master_list
2. Create a table or range somewhere in your book with two columns. The first column contains the master/look-up value and the second the dependent values.
   • I have named the list of look-up values (this is, the values in the first column) as dependent_list
   • I have named heading to the cell above the dependent_list (for example, if the dependency table starts in A2, the heading is A1
3. Order the lists
   • The master_list is ordered as you wish values appear in the dropdown list.
   • The dependent_list must be ordered by the values in the first column (master values). It doesn't matter if the order is the same in the master_list and the dependent_list, but identical values in the master column of the dependent_list must be together.
   • The order in the second column of the dependent_list will determine the order in the dependent dropdown list.
4. Optionally you can define a message to display in the dependent_list when there is no master value selected. I have named it msg_error.
5. Click on the cell where you want the master dropdown, ant go to the Data Validation menu, where you select:
   • Allow: List
   • Source: Add the formula =INDIRECT("master_list")
6. I have named the cell where the master dropdown list as key_value
7. Click on the cell where you want the dependent dropdown, and then go to the Data Validation menu, where you select:
   • Allow: List
   • Source: Add the formula =IF(key_value="";msg_error;OFFSET(heading;MATCH(key_value;dependent_list;0);1;COUNTIF(dependent_list;key_value);1))

Note that Excel functions are language dependent of the language and I have translated them to English, so maybe there are mistakes.