Forum Discussion

Artemio Valdes's avatar
Artemio Valdes
Copper Contributor
Dec 22, 2017

Excel Data Validation List Error

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.

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    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!

    • Artemio Valdes's avatar
      Artemio Valdes
      Copper Contributor
      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.
      • Bighouse's avatar
        Bighouse
        Copper Contributor

        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.

         

Resources