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
[TABLE="width: 359"]
<tbody>[TR]
[TD="class: xl65, width: 100"]Noodle[/TD]
[TD="class: xl65, width: 141"]2800-China[/TD]
[TD="class: xl65, width: 118"]100123-China[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 100"]Sandwich[/TD]
[TD="class: xl65, width: 141"]0100-Germany[/TD]
[TD="class: xl65, width: 118"]100631-Germany[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 100"][/TD]
[TD="class: xl65, width: 141"]0300-Austria[/TD]
[TD="class: xl65, width: 118"]100571-Austria[/TD]
[/TR]
</tbody>[/TABLE]
#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...
[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

3​

[/td][td]

Type​

[/td][td]

Category​

[/td][td]

Breed​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Fish[/td][td]Marine[/td][td]Shark[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]1. create a range name for the main category[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]2. create a range name for each sub-category, based on what it is[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]for mine, the main category is called Type (A2)[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]for the sub categories I give them the same name as in Type (B2:E2)[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td](if you have another level, you would repeat this for eacg subcategory, to get sub-sub-categries)[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]To get the DD's to work...[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]DD for B4 =TYPE[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]DD for C4 =INDIRECT(SUBSTITUTE(B4," ","_"))[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
This would get used with the following data table...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

2​

[/td][td]Type[/td][td]Mammal[/td][td]Bird[/td][td]Fish[/td][td]Bug[/td][td][/td][td]Dog[/td][td]Cat[/td][td]Fresh Water[/td][td]Marine[/td][/tr]

[tr][td]

3​

[/td][td]Mammal[/td][td]Dog[/td][td]Domestic[/td][td]Fresh Water[/td][td]Insect[/td][td][/td][td]Doberman[/td][td]Lion[/td][td]Trout[/td][td]Shark[/td][/tr]

[tr][td]

4​

[/td][td]Bird[/td][td]Cat[/td][td]Wild[/td][td]Marine[/td][td]Arachnid[/td][td][/td][td]Poodle[/td][td]Tiger[/td][td]Minow[/td][td]Barracuda[/td][/tr]

[tr][td]

5​

[/td][td]Fish[/td][td]Cow[/td][td][/td][td][/td][td][/td][td][/td][td]bulldog[/td][td]House[/td][td]Pike[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Bugs[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]