Dependant Drop Down Validation With Illegal Characters (&)

[BigBlueBear]

Hoping someone can help. Trying to use dependant drop downs where one set of data includes an ampersand (Excel illegal character). I can get everything to work apart from the dataset which includes Z&P in the primary dataset and have been unable to get this example to work either, even when i replicate the fruit example Easy Steps Excel Dependent Drop Down List Data Validation.

Any help would be really appreciated

 

[Snakehips]

@BigBlueBear Welcome
Might this help?
Name the Z&P list as "ZandP"
Then use the the blow =INDIRECT... statement to define the dependant DV list for column L.
Like.

=INDIRECT(SUBSTITUTE($K2,"&","and"))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1											Group Pick	Next Pick
2	Group		Corporate		Z&P		Finance		Other		Z&P	Sales
3	Corporate		HO		Sales		Control		Legal		Finance	Control
4	Z&P		Z&P (HO)		Marketing		Finance		tax		Corporate	Z&P (HO)
5	Finance		Z&P (Finance)		Data						Other	tax
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
K2:K5	List	=Group
L2:L5	List	=INDIRECT(SUBSTITUTE($K2,"&","and"))

 

[Akuini]

@BigBlueBear, welcome to the Forum.
I just discussed a similar problem in this thread:

Cascading Dropdown using table with duplicates

Hi Everyone, Below I have the following worksheet sample: ABCDEFGH1NameDivisionCenterJDSelect NameSelect Center2Adams, StanAU1Dropdown of EmpNameDropdown with Center(s) based on G23Adams, StanAV1Carson, Rita4Butler, TeddyBU25Butler, TeddyBV26Carson, RitaCX37Carson, RitaCY18Day...

www.mrexcel.com

in this case, you only need 1 table so it's easier to maintain. And there's a part that shows how to deal with illegal character in named range.

 

[BigBlueBear]

@BigBlueBear Welcome
Might this help?
Name the Z&P list as "ZandP"
Then use the the blow =INDIRECT... statement to define the dependant DV list for column L.
Like.

=INDIRECT(SUBSTITUTE($K2,"&","and"))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1											Group Pick	Next Pick
2	Group		Corporate		Z&P		Finance		Other		Z&P	Sales
3	Corporate		HO		Sales		Control		Legal		Finance	Control
4	Z&P		Z&P (HO)		Marketing		Finance		tax		Corporate	Z&P (HO)
5	Finance		Z&P (Finance)		Data						Other	tax
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
K2:K5	List	=Group
L2:L5	List	=INDIRECT(SUBSTITUTE($K2,"&","and"))

Many thanks works a treat. Your help is very much appreciated

 

[BigBlueBear]

@BigBlueBear, welcome to the Forum.
I just discussed a similar problem in this thread:

Cascading Dropdown using table with duplicates

Hi Everyone, Below I have the following worksheet sample: ABCDEFGH1NameDivisionCenterJDSelect NameSelect Center2Adams, StanAU1Dropdown of EmpNameDropdown with Center(s) based on G23Adams, StanAV1Carson, Rita4Butler, TeddyBU25Butler, TeddyBV26Carson, RitaCX37Carson, RitaCY18Day...

www.mrexcel.com

in this case, you only need 1 table so it's easier to maintain. And there's a part that shows how to deal with illegal character in named range.

Many thanks will have a look @ your post