Data Validation Dependent List (Using Indirect and Substitute functions)

[yusbar15]

Hi, I would like to ask question on data validation dependent list that involves the use of indirect and substitute function. Once the code is chosen from drop down list. Only the relevant data will be put in the drop down list in another column. The data source is is another worksheet.

Here is the example.

Food Name Area Location

Noodle	2800-China	100123-China
Sandwich	0100-Germany	100631-Germany
	0300-Austria	100571-Austria

<tbody>
</tbody>

#Above is the data source in another worksheet.

On another worksheet, I would like to make the dependent list for data entry.

Thanks

 

[FDibbins]

Try this...

	B​	C​	D​	E​	F​
3​	Type​	Category​	Breed​
4​	Fish	Marine	Shark
5​
6​
7​	1. create a range name for the main category
8​	2. create a range name for each sub-category, based on what it is
9​
10​	for mine, the main category is called Type (A2)
11​	for the sub categories I give them the same name as in Type (B2:E2)
12​	(if you have another level, you would repeat this for eacg subcategory, to get sub-sub-categries)
13​
14​	To get the DD's to work...
15​
16​	DD for B4 =TYPE
17​	DD for C4 =INDIRECT(SUBSTITUTE(B4," ","_"))

This would get used with the following data table...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
2​	Type	Mammal	Bird	Fish	Bug		Dog	Cat	Fresh Water	Marine
3​	Mammal	Dog	Domestic	Fresh Water	Insect		Doberman	Lion	Trout	Shark
4​	Bird	Cat	Wild	Marine	Arachnid		Poodle	Tiger	Minow	Barracuda
5​	Fish	Cow					bulldog	House	Pike
6​	Bugs