Data Validation Dependent Drop Down Box Error

[MHau5]

I have a four columns on a sheet1, say like this:

Category	Sub Category1	Sub Category2	Sub Category3
Sub Category1	Value 1	Sub-cat2 value 1	Sub-cat3 value 1
Sub Category2	Value 2	Sub-cat2 value 2	Sub-cat3 value 2
Sub Category3	Value 3	Sub-cat2 value 3	Sub-cat3 value 3
Sub Category4	Value 4
Sub Category5

I'm using data validation on sheet2 to create a drop down box in a column, where the user can select the sub-category in column 1 from the drop down, using the setup below:

Allow = List
Source = 'Sheet1'$A2:A$A4

Then, in the adjacent columns, I am using another data validation rule to allow the user to grab a value related to the sub-category that was chosen, using the setup below:

Allow = List
Source = indirect(a1)

The issue is that if I grab Sub Category1 or Sub Category3, the adjacent column will return the values from those particular columns. However, if I choose Sub Category2 from the drop down, then the values don't appear.

Any help would be appreciated. TIA.

 

[Bosquedeguate]

OK ... Here is a lengthy answer ...
I hope this helps.

1) I like to create a dedicated Sheet for my Lists and any other configuration kind of information,
e.g. sheet name = "Lists"
2) on your "Lists" sheet add your table like this:

DynamicDataValidationLists.xlsm
	G	H	I	J	K
1
2	Sub Category1	Sub Category2	Sub Category3	Sub Category4	Sub Category5
3	SC1-V1	SC2-V1	SC3-V1	SC4-V1	SC5-V1
4	SC1-V2	SC2-V2	SC3-V2	SC4-V2	SC5-V2
5	SC1-V3	SC2-V3	SC3-V3	SC4-V3
6	SC1-V4			SC4-V4
7				SC4-V5
Lists

3) In the Name Manager (defined names) create 6 named lists: 1) for the SubCategories, and 5) for the SubCat Values if you. If your Sub Categories can be conveniently named like your example
SubCategory_1, SubCategory_2, .... It makes the implementation of your SubCatValues much easier.

Create named lists like you see listed below (snapshot from the Name Manager dialog box)

For your data validation where you will select your SubCategory you will set up your SubCategory list as:

For your data validation list for your SubCat Values (here is where convenient naming is helpful) the Data Validation list will be:

the value inside the "()" is the Named Range for the desired value list. Example, if SubCategory_1 was selected
this algorithn gets the "1" from the selected SubCategory, and builds the SubCat list name "SC1Values".

If you do not have conveniently named SubCategories the process is basically the same with an added step.

 

[Fluff]

However, if I choose Sub Category2 from the drop down, then the values don't appear.

Make sure that you do not have any typos in either the range names or the drop-down values.

 

[Bosquedeguate]

Make sure that you do not have any typos in either the range names or the drop-down values.

Hmm - they appear in my test sheet. (did you check yours for typos?)

Here is my test sheet:

DynamicDataValidationLists.xlsm
	E	F
1	Select a Sub Cat	Sub Category2
2	Select a Task	SC2-V3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=SCList2
F2	List	=INDIRECT("SC" & RIGHT(F1) & "Values")

 

[Fluff]

I was talking to the OP as he/she said the 2 work & 1 doesn't.