View original (French)

Dropdown list error

Solved
FleurLudeau Posted messages 6 Status Membre -  
FleurLudeau Posted messages 6 Status Membre -

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 réponses

Réponse 1 / 5
DjiDji59430 Posted messages 4277 Registration date   Status Membre Last intervention   716
 

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
Réponse 2 / 5
FleurLudeau Posted messages 6 Status Membre
 

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
Réponse 3 / 5
DjiDji59430 Posted messages 4277 Registration date   Status Membre Last intervention   716
 

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
FleurLudeau Posted messages 6 Status Membre
 

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
DjiDji59430 Posted messages 4277 Registration date   Status Membre Last intervention   716 > FleurLudeau Posted messages 6 Status Membre
 

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


Best regards

0
Réponse 4 / 5
Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur Last intervention   17 474
 

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
FleurLudeau Posted messages 6 Status Membre
 

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
Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur Last intervention   17 474 > FleurLudeau Posted messages 6 Status Membre
 

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
FleurLudeau Posted messages 6 Status Membre > Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur 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
Réponse 5 / 5
FleurLudeau Posted messages 6 Status Membre
 

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