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.
Problems With INDIRECT
This dependent drop down technique uses the INDIRECT function, to return the range with the name Vegetables.
The technique works well with a static named range, but the INDIRECT function doesn’t work with a dynamic range.
Dynamic Range Example
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))
SUM For Dynamic Range
You can SUM the dynamic named range, using the following formula in cell C2:
=SUM(NumList)
The dynamic range doesn’t cause any problems, and the result is correct — the sum is 10.
Error with INDIRECT
However, if you use the INDIRECT function within the SUM function, the result is a #REF! error.
=SUM(INDIRECT(C4))
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).
Data Validation Formula
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 my Contextures website, on the dependent data validation page.
___________
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 🙂
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.
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)
Whoops, that last formula was supposed to be:
=OFFSET(INDIRECT($B2),1,0,COUNTA(INDIRECT(B2))-1,1)
@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.
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.
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.
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 ????