Excel Data Validation List Error

[Artemio Valdes]

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.

[#2]

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!

[#3]

Hi Haytham,

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.

[#4]

Please specify what change you want to make and where?

And provide us with a screenshot and a sample of your data.

[#5]

@Artemio Valdes 

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.

[#6]

I@Bighouse 

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.