How to Create Dynamic Drop Downs?

Suppose we have items of different criteria like Fruits, Vegetables, Flowers, Animals, and Birds. Each criterion has a list of items as above:

1.

2.

3.

Here is a process to create a drop down:

Drop Down No: 1

  1. First select a cell to add the drop down, for the list of criteria.
  2. Select ‘Data’ from the ribbon.
  3. Select ‘Data validation’ from the menu.

4. Click on the ‘Data validation’ In the validation criteria, select ‘list’ in ‘allow’ box and type in the following formulae in ‘source’:

=$C$2:$G$2

The above formula indicates the range of cells in which the list of criteria is available.

5. When we click on the drop down, we will be able to see the list of criteria.

Drop Down No: 2

Now suppose we want to retrieve ‘Apple’ from criterion ‘Fruits’.

  1. Select the items of ‘Fruits’ and click in the ‘Name box’ which is at the top, left hand side of the sheet.
  2. Type the name of the criterion, ‘Fruits’ which we had selected. Then press enter key.

3. Select a cell to add the drop down, for the list of items.

4. Select ‘Data’ from the ribbon.

5. Select ‘Data validation’ from the menu.

6. In the validation criteria, select ‘list’ in ‘allow’ and type in the following formulae in ‘source’:

=INDIRECT ($D$9)

What this does is this:

=INDIRECT (ref-text, [a1])

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! Error value.

A1 is a logical value that specifies what type of reference is contained in the cell ref_text.

Here, we are using the formula in drop down. So we need to give the reference cell number (D9).

Now the drop down for list of items is added. Now the required item

can be selected from the list.

In the similar way we can do for all the criteria, and select the required item. In this way we can create dynamic drop downs in excel.

How to Change Text Like YYYYMMDD into YYYY/MM/DD format?

Often, you get text like 19950607 and you want to be able to convert it into text like 1995/06/07.  Here’s a formula you can use to convert the text into

a formula. (this assumes the text is in A1)

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

What this does is this:

Date formula has the following parameters – DATE (Year, Month, Day)

The formula I wrote about simply splits the text

into Year, Month and Day and uses it in the DATE function.

MID function allows you to split text and pick up parts of it. Here’s the syntax

MID(text, start number of digit you want to start from, number of digits)

MID(A1,7,2) – picks up the text 19950607 (i.e. A1) starts from 7 i.e. 0 and gives you two digits from there – 07

—————————————-

Excel/Financial ModelingProfitability/Cost AnalysisDashboard ReportingCustom Invoice