Results 1 to 10 of 10

Thread: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    I am using data validation for a particular column. The category list of values is pulled in from a website into a hidden sheet. I did this because I may need to frequently change the allowed values for the category list.

    The data pulls in correctly and does restrict--in the dropdown options, I am able to see any added category list item.

    However, when I manually type in the field, it generates a data validate error:

    "The value you entered is not valid."

    Why the heck would it work for a dropdown but not for a manually typed in entry? I have checked extra cell spaces, cell format type, and so on.

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,054
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    I would guess that the values pulled from the website have non printing characters, line non breaking spaces, in them that the data validation is looking for.

    Try using clean to remove non printing characters and see if that works

    https://exceljet.net/tips/how-to-cle...trim-and-clean
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    Thanks for the reply! Where would I enter in the CLEAN() or TRIM() functions? I tried in the data validate selection formula to no avail.

    The connection pulls from a table in SharePoint into an unformatted "table" in the a sheet included in the workbook.

  4. #4
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,054
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    I would try putting your current formula into clean
    Code:
    =CLEAN(your current fomula here)
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    Done. I pulled in the data from the website, and then referenced each cell with =TRIM(CLEAN(AX)) where X = 1, 2, 3, 4, etc.

    Nothing--still the same error. I do have quite a few fields for the formula to look at. Is there a limit to the number of items that data validate can look at when it let's you type it in?

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    My guess is that this is unsolvable.

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    ALL RIGHT! Found a lead:

    It's something with the first character. If I copy and paste the value it's data validating from, everything's okay. However, if I type the same data, I get an error. I replaced every letter until I found that the first letter of my name (J) is what's causing it to recognize. However, neither trim nor clean is fixing it.

    Any ideas? I feel that I'm so close!!!

  8. #8
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,054
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    Unfortunately clean does not remove all non printing characters or it could be a different problem.

    Change to point to one of your data validation options and see what code is returned to see if that is the problem. copy down if need to test whole string.
    Excel 2010
    ABCDE
    1test text here116t
    2101e
    3115s
    4116t
    532
    6116t
    7101e
    8120x
    9116t
    1032
    11104h
    12101e
    13114r
    14101e
    15

    Sheet1



    Worksheet Formulas
    CellFormula
    D1=CODE(MID($A$1,ROW(D1),1))
    E1=CHAR(D1)
    D2=CODE(MID($A$1,ROW(D2),1))
    E2=CHAR(D2)
    D3=CODE(MID($A$1,ROW(D3),1))
    E3=CHAR(D3)
    D4=CODE(MID($A$1,ROW(D4),1))
    E4=CHAR(D4)
    D5=CODE(MID($A$1,ROW(D5),1))
    E5=CHAR(D5)
    D6=CODE(MID($A$1,ROW(D6),1))
    E6=CHAR(D6)
    D7=CODE(MID($A$1,ROW(D7),1))
    E7=CHAR(D7)
    D8=CODE(MID($A$1,ROW(D8),1))
    E8=CHAR(D8)
    D9=CODE(MID($A$1,ROW(D9),1))
    E9=CHAR(D9)
    D10=CODE(MID($A$1,ROW(D10),1))
    E10=CHAR(D10)
    D11=CODE(MID($A$1,ROW(D11),1))
    E11=CHAR(D11)
    D12=CODE(MID($A$1,ROW(D12),1))
    E12=CHAR(D12)
    D13=CODE(MID($A$1,ROW(D13),1))
    E13=CHAR(D13)
    D14=CODE(MID($A$1,ROW(D14),1))
    E14=CHAR(D14)

    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,774
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    First, let's find the ASCII code for the character in the first space. That will tell us what we are dealing with here.
    If the entry was in cell A1, here is the formula to tell us that:
    Code:
    =CODE(LEFT(A1,1))
    Once we get that number, we can look here to see what we are dealing with: https://www.asciitable.com/
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    May 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

    You guys got it! Thank you so much, it was a weird, invisible leading character. Some artifact from SharePoint.

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
  •  

 

DMCA.com