Illegal Characters in Drop downs

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi, Can you please provide some examples of what your file looks like and the output you are expecting.
 
Upvote 0
I can't post an excel file, so this is the closest I can get to showing you:

Slices Of Pizza:Toppings:Percent of toppings on Pizza:
10Cheese 1/4
12Peperoni 1/8
14Bacon 1/3
16Ham 1/2

<colgroup><col><col><col></colgroup><tbody>
</tbody>


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?
 
Upvote 0
You can store the fractional values as text, entering '1/4 or '1/8 in the cell. This will work for Data Validation.
 
Upvote 0
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".
 
Upvote 0
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
 
Last edited:
Upvote 0
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
JKL
1FirstSecondThird
2B1/8
3A1/22
4A1/4X
Sheet1


Third Defined Table:


Book1
NO
11/21/4
21X
32Y
43Z
Sheet1
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top