Dynamic Dependent Excel Drop Downs

With dependent data validation, you can make one drop down list depend on the selection in another cell. For example, select Vegetables as a category in column B, and you’ll see a drop down list of vegetables in column C.

datavaldependdynamic01

Problems With INDIRECT

This technique uses the INDIRECT function, to return the range with the name Vegetables. It works well with a static named range, but INDIRECT doesn’t work with a dynamic range.

For example, in the workbook shown below, there is a dynamic range named NumList, with the formula:

=Admin!$A$1:INDEX(Admin!$A:$A,COUNTA(Admin!$A:$A))

You can SUM the dynamic named range:

=SUM(NumList)

for the correct result of 10.

datavaldependdynamic02

However, if you use the INDIRECT function, the result is a #REF! error.

=SUM(INDIRECT(C4))

datavaldependdynamic03

Dependent Data Validation Workaround

Instead of using dynamic ranges with dependent data validation, you can use a named starting cell (Vegetables) and named column (VegetablesCol).

datavaldependdynamic04

Then, refer to those named ranges in the dependent data validation formula.

=OFFSET(INDIRECT($B2),0,0,COUNTA(INDIRECT(B2&”Col”)),1)

You can see this example, and a formula that substitutes invalid characters, on the Contextures website, dependent data validation page.

___________

You may also like...

11 Responses

  1. SteveT says:

    I love offset, but it definitely can slow down a spreadsheet from time to time. So I was hoping to switch to Indirect and Match, but now you have talked me out of it :)

  2. Jeff Weir says:

    Great timing, Deb…here I was pulling my hair out just today because I kept getting a REF error.

    I’ve got out of the habit of using OFFSET for dynamic ranges, and use INDEX instead, with the general form:
    =$A$2:INDEX($A:$A,COUNTA($A:$A))
    …where the dynamic list is in column A, with the header in A1.

    I wonder if there would be any advantage using an amended syntax using this form rather than the OFFSET one i.e.:
    INDIRECT($B$2):index(INDIRECT(B2&”Col”),COUNTA(INDIRECT(B2&”Col”)))
    …on account that it does away with the volatile OFFSET function yet still has a volatile INDIRECT function in there?

    Note that for this to work, your ‘starting cell name’ would have to be the first record, and not the column header.

  3. Jeff Weir says:

    On 2nd thought, I think sticking with OFFSET is easier to understand.

    By the way, you don’t actually need to name both the starting cell AND the column as you have done in your example above. You can get away with just naming the column. And if you have a header row, you can trim the result down to size by amending the ROWS and HEIGHT arguments of the OFFSET function accordingly.

    So if we have no header row, then this will do it:
    =OFFSET(INDIRECT($B2),0,0,COUNTA(INDIRECT(B2)),1)
    …and if we have a header row, then this will do it:
    =OFFSET(INDIRECT($B2),1,0,COUNTA(INDIRECT(B2&”Col”))-1,1)

  4. Jeff Weir says:

    Whoops, that last formula was supposed to be:
    =OFFSET(INDIRECT($B2),1,0,COUNTA(INDIRECT(B2))-1,1)

  5. @Jeff, thanks for the revised formulas! I played with the INDEX option before posting this article, and decided to stick with OFFSET for this example. As you said, it seems easier to understand in this context.

  6. Gary says:

    Hi

    I don’t no if i can ask a question,im looking for away of having dynamic dependant drop down list like this say you had a selection of engine Manufactures in the first drop down that you could update and sort by typing into the drop down box, and the same in the dependant drop down box this would be named type.

  7. Randy says:

    Is there any way to allow a cell to sometimes use a list for data validation and at other times to allow free data entry? In other words using your data above, with an entry of vegetables in B3 I get a list in C3. But perhaps the list is too variable for Fruit so I want the C2 cell to allow a free text entry. Thus the data validation method in column C is dependent of the value in column B.

  8. Prakash says:

    I have a data validation in a row in a way that 10 digit nos. only are entered in the cells. However now i would also like that there are no duplicate entries made in cells in the same column.
    I use the countif function for restricting duplicate entries.
    Can you help me with this problem ????

  1. March 9, 2013

    […] werkt niet met dynamische genaamde bereiken, zie b.v. Dynamic Dependent Excel Drop Downs | Contextures Blog Zie bijlage voor een mogelijk alternatief met "Choose" Bijgevoegde […]

  2. April 21, 2013

    […] fry pan into the fire. Debra D. does a great job here to get you going with depentent drop-downs. Dynamic Dependent Excel Drop Downs | Contextures Blog Perhaps another way to go is with a single drop down where your list is like this: TX followed by […]

  3. April 23, 2013

    […] IraAxa, welcome to the fourm. Take a look here for dependant drop downs. Dynamic Dependent Excel Drop Downs | Contextures Blog Regards, […]

Leave a Reply to Gary Cancel reply

Your email address will not be published. Required fields are marked *