Illegal Characters in Drop downs

[ERed1]

Hey everyone,

Does anyone know how to use illegal characters in a Drop down? I have looked it up, but everything I have read went over my head. I have a a drop down that is dependent on a drop down. In the first drop down I have a few fractions that need to be options. Thanks for the help.

 

[Aryatect]

Hi, Can you please provide some examples of what your file looks like and the output you are expecting.

 

[ERed1]

I can't post an excel file, so this is the closest I can get to showing you:

[TABLE="width: 386"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Slices Of Pizza:[/TD]
[TD]Toppings:[/TD]
[TD]Percent of toppings on Pizza:[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Cheese[/TD]
[TD="align: right"] 1/4[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Peperoni[/TD]
[TD="align: right"] 1/8[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Bacon[/TD]
[TD="align: right"] 1/3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Ham[/TD]
[TD="align: right"] 1/2[/TD]
[/TR]
</tbody>[/TABLE]


So if I were to do the data validation lists, I would make named ranges. The named ranges don't allow the "/" in the name. Is there a way to have the drop down list work with the '/'? Or is there another, better way?

 

[Aryatect]

You can store the fractional values as text, entering '1/4 or '1/8 in the cell. This will work for Data Validation.

 

[ERed1]

I did that and it worked sort of. There is one more Data Validation List that is dependent on this fraction. The named ranged switches it to "'1_4" or "1_8".

 

[Aryatect]

ohh I get you now, the Name Manager is not allowing you to change the name for the fraction. Sorry but as far as I know it does not allow to have that character in the name manager,

if the value is 1/4 you can use =INDIRECT("_"&SUBSTITUTE(C2,"/","_")) in data validation assuming that excel will change the name 1/4 to _1_4

 

[ERed1]

Where would I put that at?

 

[Aryatect]

So for below case I have put the formula in Data validation for Column L pointing to Column K. and defined 1/2 value as _1_2 in Name Manager:

Book1
	J	K	L
1	First	Second	Third
2	B	1/8
3	A	1/2	2
4	A	1/4	X
Sheet1

Third Defined Table:

Book1
	N	O
1	1/2	1/4
2	1	X
3	2	Y
4	3	Z
Sheet1

 

[Aryatect]

So in this case selecting Column L and putting the Data Validation of for List and Source for it

Where would I put that at?

 

[ERed1]

So I tried the substitute version, and I had no luck. I typed =INDIRECT(SUBSTITUTE(C2,"/","_")) into the data validation source. I think the dependent validation is not wokring because it still has the previous one showing as "1/2", while the name still shows "1_2"