Data Validation Drop-Down List Reference in Another Sheet

[miffy45]

Hello,

Could somebody help?
I have data in Sheet 'Reference' as per below:

	A​	B​
1​	Category1​	Category2​
2​	Cat1.1​	Cat2.1​
3​	Cat1.2​	Cat2.2​

An in the other sheet named "Data Input", in column B for option i should have a drop-down refering a data in sheet 'reference' and the result depends on what i fill in column A in this sheet.
For example, if i fill Category1 in column A then the drop-down list in column B should be showing Cat1.1 and Cat1.2

	A​	B​
1​	Category​	Option​
2​

I already seek for solution and most of it using Indirect function, but after i try it's only applicable if the reference data on the same sheet as the input column.

Thank you.

 

[kevin9999]

There's a number of ways to do it. Personally, I would use a helper column on the Data Input sheet and use the Filter function (I note you have 365) like this:

Book1
	A	B
1	Category1	Category2
2	Cat1.1	Cat2.1
3	Cat1.2	Cat2.2
Reference

Book1
	A	B	C
1	Category	Option
2	Category2	Cat2.1	Cat2.1
3			Cat2.2
Data Input

Cell Formulas
Range	Formula
C2:C3	C2	=FILTER(Reference!$A$2:$B$3,Reference!$A$1:$B$1='Data Input'!$A$2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Reference!$A$1:$B$1
B2	List	=C2#

 

[miffy45]

Hi Kevin,

Thank you for your answer.
I've tried with simple sample above and it worked, but i think it didn't applies to my condition later.

Since later the category in Data Input will be added until like 5-10 rows.
The filter will stacked and hard to maintain because the option will have numerous rows too.

Really appreciate for your fast response tho.

 

[kevin9999]

Hi Kevin,

Thank you for your answer.
I've tried with simple sample above and it worked, but i think it didn't applies to my condition later.

Since later the category in Data Input will be added until like 5-10 rows.
The filter will stacked and hard to maintain because the option will have numerous rows too.

Really appreciate for your fast response tho.

I don't think it'll be as difficult as you imagine. Can you provide a sample of your actual data using the XL2BB add in (or share your file via Dropbox, Google Drive or similar) and I'll demonstrate?

 

[kevin9999]

I don't think it'll be as difficult as you imagine.

This is what I mean...

Book1
	A	B	C	D	E	F	G	H	I	J
1	Category1	Category2	Category3	Category4	Category5	Category6	Category7	Category8	Category9	Category10
2	Cat1.1	Cat2.1	Cat3.1	Cat4.1	Cat5.1	Cat6.1	Cat7.1	Cat8.1	Cat9.1	Cat10.1
3	Cat1.2	Cat2.2	Cat3.2	Cat4.2	Cat5.2	Cat6.2	Cat7.2	Cat8.2	Cat9.2	Cat10.2
4	Cat1.3	Cat2.3	Cat3.3	Cat4.3	Cat5.3	Cat6.3	Cat7.3	Cat8.3	Cat9.3	Cat10.3
5	Cat1.4	Cat2.4	Cat3.4	Cat4.4	Cat5.4	Cat6.4	Cat7.4	Cat8.4	Cat9.4	Cat10.4
6	Cat1.5	Cat2.5	Cat3.5	Cat4.5	Cat5.5	Cat6.5	Cat7.5	Cat8.5	Cat9.5	Cat10.5
7	Cat1.6	Cat2.6	Cat3.6	Cat4.6	Cat5.6	Cat6.6	Cat7.6	Cat8.6	Cat9.6	Cat10.6
8	Cat1.7	Cat2.7	Cat3.7	Cat4.7	Cat5.7	Cat6.7	Cat7.7	Cat8.7	Cat9.7	Cat10.7
9	Cat1.8	Cat2.8	Cat3.8	Cat4.8	Cat5.8	Cat6.8	Cat7.8	Cat8.8	Cat9.8	Cat10.8
10		Cat2.9	Cat3.9	Cat4.9	Cat5.9	Cat6.9	Cat7.9	Cat8.9	Cat9.9	Cat10.9
11		Cat2.10	Cat3.10	Cat4.10	Cat5.10			Cat8.10	Cat9.10	Cat10.10
12		Cat2.11	Cat3.11	Cat4.11	Cat5.11			Cat8.11	Cat9.11	Cat10.11
13			Cat3.12	Cat4.12	Cat5.12			Cat8.12	Cat9.12	Cat10.12
14			Cat3.13	Cat4.13	Cat5.13			Cat8.13	Cat9.13	Cat10.13
15			Cat3.14	Cat4.14	Cat5.14			Cat8.14	Cat9.14	Cat10.14
16			Cat3.15	Cat4.15	Cat5.15			Cat8.15	Cat9.15	Cat10.15
17			Cat3.16	Cat4.16	Cat5.16			Cat8.16	Cat9.16	Cat10.16
18			Cat3.17	Cat4.17	Cat5.17			Cat8.17	Cat9.17	Cat10.17
19			Cat3.18	Cat4.18	Cat5.18			Cat8.18	Cat9.18	Cat10.18
20
21
22
23
24
25
Reference

Book1
	A	B	C
1	Category	Option
2	Category9	Cat9.2	Cat9.1
3			Cat9.2
4			Cat9.3
5			Cat9.4
6			Cat9.5
7			Cat9.6
8			Cat9.7
9			Cat9.8
10			Cat9.9
11			Cat9.10
12			Cat9.11
13			Cat9.12
14			Cat9.13
15			Cat9.14
16			Cat9.15
17			Cat9.16
18			Cat9.17
19			Cat9.18
20
21
Data Input

Cell Formulas
Range	Formula
C2:C20	C2	=UNIQUE(FILTER(Reference!$A$2:$J$25,Reference!$A$1:$J$1='Data Input'!$A$2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Reference!$A$1:$J$1
B2	List	=C2#

You can even hide the column with the returned options.

Book1
	A	B		D
1	Category	Option
2	Category9	Cat9.2
3
4
Data Input

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Reference!$A$1:$J$1
B2	List	=C2#

 

[kvsrinivasamurthy]

Select B2:B10 (or as required) in reference sheet. Formula for DV.

=OFFSET('Data Input'!$A$2,0,MATCH($A2,'Data Input'!$A$1:$G$1,0)-1,COUNTA(INDEX('Data Input'!$A$1:$G$20,,MATCH($A2,'Data Input'!$A$1:$G$1,0)))-1)

Adjust the ranges as required.

 

[miffy45]

Hi Kevin,

Thank you for the explanation and sample.
Then how do you done for the 2nd row? in Data Input Sheet.

Is Cell B3 and C3 have same formula?
I got stuck with this one, since i cannot do this in Cell C3 due to cell occupied from C2 filter result.

Range	Formula
Cell Formulas
C2:C20	C2	=UNIQUE(FILTER(Reference!$A$2:$J$25,Reference!$A$1:$J$1='Data Input'!$A$2))
Dynamic array formulas.

Kindly tell me if there's anything wrong with what i'm doing?
Sorry currently i cannot upload the file with drive, drive error.

 

[kevin9999]

If you wanted to add additional Categories down column A, and still use the same methodology, then you would need to add additional 'help' columns along the sheet. Like this:

miffy45.xlsx
	A	B	C	D	E
1	Category	Option
2	Category9	Cat9.2	Cat9.1	Cat6.1	Cat10.1
3	Category6	Cat6.4	Cat9.2	Cat6.2	Cat10.2
4	Category10	Cat10.3	Cat9.3	Cat6.3	Cat10.3
5			Cat9.4	Cat6.4	Cat10.4
6			Cat9.5	Cat6.5	Cat10.5
7			Cat9.6	Cat6.6	Cat10.6
8			Cat9.7	Cat6.7	Cat10.7
9			Cat9.8	Cat6.8	Cat10.8
10			Cat9.9	Cat6.9	Cat10.9
11			Cat9.10	0	Cat10.10
12			Cat9.11		Cat10.11
13			Cat9.12		Cat10.12
14			Cat9.13		Cat10.13
15			Cat9.14		Cat10.14
16			Cat9.15		Cat10.15
17			Cat9.16		Cat10.16
18			Cat9.17		Cat10.17
19			Cat9.18		Cat10.18
20			0		0
21
Data Input

Cell Formulas
Range	Formula
C2:C20	C2	=UNIQUE(FILTER(Reference!$A$2:$J$25,Reference!$A$1:$J$1='Data Input'!$A$2))
D2:D11	D2	=UNIQUE(FILTER(Reference!$A$2:$J$25,Reference!$A$1:$J$1='Data Input'!$A$3))
E2:E20	E2	=UNIQUE(FILTER(Reference!$A$2:$J$25,Reference!$A$1:$J$1='Data Input'!$A$4))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:A4	List	=Reference!$A$1:$J$1
B2	List	=C2#
B3	List	=D2#
B4	List	=E2#

As I mentioned earlier, you can always hide those helper columns if you wish.

 

[miffy45]

Hi Kevin,

Ok, it's do-able for now.

Thank you for your time and insights!