View original (French)

Dropdown list error

Solved
FleurLudeau Posted messages 6 Status Member -  
FleurLudeau Posted messages 6 Status Member -

Hello,

I have a management file in which I have a tab dedicated to recording my new clients. One column of this tab is then used as a base for drop-down lists all over the file.

When I check the entries in my drop-down lists, all the clients are well listed. However, some names do not appear when I search for them, and I get an error "Invalid: the input value must be within the specified range."

Once again, I have checked that the searched name is present, with the same spelling in the list.

Thank you for your help!

5 answers

  1.
    DjiDji59430 Posted messages 4280 Registration date   Status Member Last intervention   718
     

    Hello everyone,

    did your check also involve looking for any stray spaces (before-after) or an accent that might be lost?

    On the other hand, if you have "Invalid: the input value must be within the specified range," what will you write in a drop-down list?


    Best regards

    0
  2. FleurLudeau Posted messages 6 Status Member
     

    Thank you for your response, yes I have indeed checked the accents, spaces, and capital letters.

    Once again, when I check the settings of my data validation, my client's name is indeed on the list, which surprises me.

    So when I get the error, I go back to my client database and create it again, and it works. But this is absolutely not optimized!

    0
  3. DjiDji59430 Posted messages 4280 Registration date   Status Member Last intervention   718
     

    Hello,

    - - - An EXCEL file (test or not), completed with exhaustive explanations and filled-in examples, uploaded to https://www.cjoint.com/, as well as your version of Excel, would allow the participants to answer your question more accurately.
    Best regards

    0
    1. FleurLudeau Posted messages 6 Status Member
       

      Hello,

      I'm working on Google Sheets because it's a file that needs to be editable by my entire team in real time.

      Unfortunately, I can't share it due to confidentiality issues.

      But in principle, I totally agree with you!

      0
      1. DjiDji59430 Posted messages 4280 Registration date   Status Member Last intervention   718 > FleurLudeau Posted messages 6 Status Member
         

        But you can upload the anonymized file, with the case that is causing you trouble.


        Best regards

        0
  4. Raymond PENTIER Posted messages 58229 Registration date   Status Contributor Last intervention   17 494
     

    Hello.

    It's not rocket science: if your current client list is in the range G2:G30, you replace G30 with G999.

    Check:
    What is the range listed in Data Validation/List/Source?
    And what rank does your "invalid" client have?

    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)

    0
    1. FleurLudeau Posted messages 6 Status Member
       

      Already done, my dropdown list is set up on $B2:$B and I checked, it updates correctly with my latest entries.

      Once again, when I check the entries in the list, the client causing the error is indeed present in the list, it drives me crazy! ^^

      0
      1. Raymond PENTIER Posted messages 58229 Registration date   Status Contributor Last intervention   17 494 > FleurLudeau Posted messages 6 Status Member
         

        Not having access to your file, we unfortunately can't do anything more!

        Could you at least provide us with the exact content (with spaces and special characters) of the cell concerning this client?

        0
      2. FleurLudeau Posted messages 6 Status Member > Raymond PENTIER Posted messages 58229 Registration date   Status Contributor Last intervention  
         

        I understand that it's not obvious without the file.

        The problem occurs with different clients, but for example, I have "Elina Boullé" without any space before or after. She appears correctly in the entries of my dropdown list when I check. However, when I call the client in a cell that is validated by this list, the error appears.

        In the same file, other sheets reference this same list without any issue. Therefore, I will try to paste the data into a new sheet and then set up the list again to see if it works.

        0
  6. FleurLudeau Posted messages 6 Status Member
     

    Problem solved! The hiccup was in the definition of my dropdown list from the start :S

    It was set to the range $B7:$B instead of $B$7:$B ...

    So silly, I'm sorry for making you rack your brains for so little!

    0