Dec 22 2017 10:21 AM
Hello, Last mont or so i created an excel with a data Validation like this:
=IF($C$2="DAM-1",'Hiden Data'!$B$2:$B$5,IF($C$2="DAM-2",'Hiden Data'!$B$6:$B$8,IF($C$2="DISCO FAN LEAP-A",'Hiden Data'!$B$9:$B$20,IF($C$2="DISCO FAN LEAP-B",'Hiden Data'!$B$21:$B$32,IF($C$2="DISCO FAN-7",'Hiden Data'!$B$33:$B$34)))))
But now i tried to modify and get this error:
the list source must be a delimited list or reference to single row or column using if
even if i try to use the old code the error keeps showing, when it didn't before.
There's a way i can solve this witout defining a name for the range and transform them in tables because that interferes with a macro i have in the workbook.
Dec 22 2017 11:23 AM
The data validation formula is valid!
Nothing wrong with it!
Read the error message well, it's straightforward!
It means that the data validation list must be a one-way list (single column range or single row range), or a delimited list like this: A, B, AB, CD.
Range("A1:A10") Valid
Range("A1:D1") Valid
Range("A1:C10") Invalid
Follow these conditions and you will get rid of that error message!
Dec 22 2017 11:59 AM
Dec 22 2017 08:43 PM
Please specify what change you want to make and where?
And provide us with a screenshot and a sample of your data.
Nov 04 2020 01:48 AM - edited Nov 04 2020 01:52 AM
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.
Oct 18 2022 08:22 AM
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.