Subscribe to andrewsw's Blog        RSS Feed
-----

Excel with Andy: Dynamic Drop-Down

Icon 1 Comments
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.

Posted Image

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)

Posted Image

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

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

March 2020

S M T W T F S
1234567
891011121314
15161718192021
22232425262728
29 30 31    

Tags

    Recent Entries

    Recent Comments

    Search My Blog

    0 user(s) viewing

    0 Guests
    0 member(s)
    0 anonymous member(s)

    Categories