Forum Discussion
Excel Data Validation List Error
As you can see in the code, all the options are in the same Coulmn (B).
The code worked properly before, but now it doesn´t, and that is exactly the proplem.
I only wanted to change one name option, but the error appeared. I canceled, opened again, and the data validation works, but if i open the data validation it will pop the error, even if i just opened and click acept without modifying anything.
And because i really need to modify it, its a problem.
I had the very same issue. A validation formula previously working smoothly returned that alert once a new argument was added.
After a while I realized that if the cell you are checking contains already the value you are entering as filter for your validation list you get that alert. I solved deleting the relevant value from the cell before adding the new argument in the validation formula. New validation formula was then accepted. When writing the value again in the cell the validation was working perfectly.
Not sure I've been clear enough so I will write an example. My formula was something like:
=IF(AND(A1<>"RS",A1<>"GA",A1<>"GG",A1<>"YM", B1<>"I",B1<>"CS",C1<>"PREC"),KPI_List)
You can see that I was checking the values of various cells at the same time. I had to add another check (adding it at the beginning of the AND function below):
=IF(AND(A1<>"MJ", A1<>"RS",A1<>"GA",A1<>"GG",A1<>"YM", B1<>"I",B1<>"CS",C1<>"PREC"),KPI_List)
This returned the alert you are describing. The value "MJ" was in cell A1. I deleted, "MJ" from the cell A1 and tried again to modify the formula. This time it worked and when writing back "MJ" in cell A1 the validation was correctly working. Strange behavior.
If a validated cell returns an empty value (""), Excel throws an error because there’s no list to validate. For example, if B2 has =IF(A1=0,"C1:C4",""), and A1 is not zero, the validation list disappears.
Fix: Ensure the validation formula always returns a valid list or range of at least one cell—not just a number or string (e.g., =IF(A1=0,1,"") or =IF(A1=0,"A1 is zero","") ). If needed, erase the existing validation formula and reapply it.
I had the same error. It was because i was trying to validate data in merged cells. I unmerged the cells and it cleared the error.
This is also what worked for me. Excel is reading each cell instead of counting the merged cells as one.
