Subscribe to andrewsw's Blog

## Excel with Andy: Dynamic Drop-Down

Using Defined Names (formerly called Range Names) and Data Validation we can create a drop-down list where the list of choices changes according to the value chosen in an adjacent cell. In the screenshot the list of items in the drop-down changes according to whether Fruit or Veg is chosen in the adjacent cell in column A.

Type the data shown on the spreadsheet.

Highlight column D. On the Formulas tab, choose Define Name in the Defined Names group. Name this column Fruit. Do the same for column E and Veg.

Select A2 to A9. On the Data tab, choose Data Validation. Change 'Allow:' to List and in 'Source:' type Fruit,Veg.

Click into B2. We will create a another defined name that uses a formula. The cell-referencing used in the formula will be relative to where we are in B2, so it is important that we are in this cell.

Create another defined name Items using this formula:

=OFFSET(INDIRECT(Sheet1!\$A2),1,0,COUNTA(INDIRECT(Sheet1!\$A2))-1,1)

This formula obtains a range, which will either be D2:D5 or E2:E4 depending on what is chosen in A2 (or A3, etc.).

Let's say that Fruit is entered in A2. INDIRECT(Fruit) identifies column D. So we now have:

=OFFSET(INDIRECT(Fruit),1,0,COUNTA(INDIRECT(Fruit))-1,1)

which is

=OFFSET(D:D,1,0,COUNTA(D:D)-1,1)

or

=OFFSET(D1,1,0,COUNTA(D:D)-1,1)

since OFFSET starts from a single reference-cell.

COUNTA(D:D) counts all the occupied cells in column D, so 5. Subtracting 1 we have:

=OFFSET(D1,1,0,4,1)

This identifies a range which is one row down from D1, 0 columns across, and of height 4 and width 1. So, D2:D5. If, instead, Veg is entered in A2, then the identified range is E2:E4.

Finally, select B2:B9 and apply Data Validation, using List and setting the Source to =Items. (We have to create the defined name first, we cannot directly use a formula as the source for a Data Validation list.)

### 1 Comments On This Entry

Page 1 of 1

#### t3cho

17 April 2015 - 01:25 PM
I was looking for this. Thank you for contribution
0
Page 1 of 1

There are no Trackbacks for this entry

S M T W T F S
12
3456789
10111213141516
1718 19 20212223
24252627282930
31