Data Validation Drop-Down List Reference in Another Sheet

miffy45 · Apr 18, 2023

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 · Apr 18, 2023

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

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 · Apr 18, 2023

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 · Apr 18, 2023

miffy45 said:
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 · Apr 18, 2023

kevin9999 said:
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 · Apr 18, 2023

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

Excel Formula:

=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 · Apr 19, 2023

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 · Apr 19, 2023

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

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 · Apr 19, 2023

Hi Kevin,

Ok, it's do-able for now.

Thank you for your time and insights!

Data Validation Drop-Down List Reference in Another Sheet

miffy45

New Member

Excel Facts

kevin9999

Well-known Member

miffy45

New Member

kevin9999

Well-known Member

kevin9999

Well-known Member

kvsrinivasamurthy

Well-known Member

miffy45

New Member

Attachments

kevin9999

Well-known Member

miffy45

New Member

Similar threads

Forum statistics

Share this page

Data Validation Drop-Down List Reference in Another Sheet

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Attachments

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock