Illegal Characters in Drop downs
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Illegal Characters in Drop downs

  1. #1
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Illegal Characters in Drop downs

    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.

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Illegal Characters in Drop downs

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

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Illegal Characters in Drop downs

    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:
    10 Cheese 1/4
    12 Peperoni 1/8
    14 Bacon 1/3
    16 Ham 1/2


    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?

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Illegal Characters in Drop downs

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

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Illegal Characters in Drop downs

    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".

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Illegal Characters in Drop downs

    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 by Aryatect; Jul 5th, 2019 at 12:36 PM. Reason: updating data validation
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Illegal Characters in Drop downs

    Where would I put that at?

  8. #8
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Illegal Characters in Drop downs

    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:

    JKL
    1FirstSecondThird
    2B1/8
    3A1/22
    4A1/4X

    Sheet1





    Third Defined Table:

    NO
    11/21/4
    21X
    32Y
    43Z

    Sheet1



    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  9. #9
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Illegal Characters in Drop downs

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

    Quote Originally Posted by ERed1 View Post
    Where would I put that at?
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    Board Regular
    Join Date
    Jun 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Illegal Characters in Drop downs

    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"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •