Dependant data validation dropdown list

[eduzs]

My worksheet has a range like (hypothetical, just an example):
Type: Name:
1 Orange
1 Blue
2 White
2 Green
2 Black
What I want to do is, when A1 value is "1" or "2" the dropdown/data validation in B1 will show only the correpondent names.
A1="1", shows "Orange, Blue"
A2="2", shows "White, Green, Black"
Is that possible without VBA or creating subtables for each type?
Thanks.

 

[Yongle]

Cannot use 1 and 2 as names for named ranges (not permitted names)

Use a helper column B to "convert" 1 to ONE and 2 to TWO (permitted names)
formula :
=CHOOSE(A2,"ONE","TWO")

Create named ranges ONE and TWO containing required values

Data validation formula in C2
=INDIRECT(B2)

 

[jasonb75]

Cannot use 1 and 2 as names for named ranges (not permitted names)

But _1 and _2 are permitted, then you could use =INDIRECT("_"&A2)

 

[eduzs]

Thanks!

 

[Peter_SSs]

Is that possible without VBA or creating subtables for each type?

Depends what Excel version you are using. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have Excel 365 with the FILTER functions then you might be able to use this.

Place the formula shown in F1 only and the other results will automatically 'spill' down the column.
Then set up the simple Data Validation as shown below,

20 05 10.xlsm
	A	B	C	D	E	F
1	2			Type	Name	White
2				1	Orange	Green
3				1	Blue	Black
4				2	White
5				2	Green
6				2	Black
7
Dependant DV

Cell Formulas
Range	Formula
F1:F3	F1	=FILTER(E2:E6,D2:D6=A1,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=F1#

Result:

 

[eduzs]

Now I'm using both Word 2019 and Word 2010, sheet/vba code should work both version.
I believe that it is not possible to create an all-in-one (single function, no additional range/columns needed) data validation /dropdown list, so currently I'm using another solution.
Thanks!

 

[Peter_SSs]

Now I'm using both Word 2019 and Word 2010,

Presumably you mean Excel in relation to this forum.
It still would help if you put those versions in your profile so helpers always know and don't waste time developing solutions that are irrelevant because of your version(s).
I see that you have Excel 2010 in your signature but not 2019. In any case, in this new forum, helpers will more likely be looking left for your version(s) not at your signature.

 

[eduzs]

Sorry, I mean Excel. ?
I will update my account details.
Looking here in my old notes I found a formula (list validation rule) that works perfectly!
=OFFSET(Tables!$A$1,MATCH(F2,Tables!$A$1:$A$10,0)-1,1,COUNTIF(Tables!$A$1:$A$10,F2))
(translated)
Where:
Tables!A1:A10 = table of Types and colors with column label (1st row).
F2 = user select a type
This formula only requires that the table be in order of "types" and "colors" in alphabetical order.

 

[Peter_SSs]

I will update my account details.

Thanks for doing so.