Forum Discussion

Noah_Zouine's avatar
Noah_Zouine
Copper Contributor
Nov 16, 2023

Problems with formulas and data validation lists

I am having a problem with creating a formula combined with a data validation list. In Column T, cell T12 I have a data validation list with the options "Yes" and "No". In Column N, cell N12 I also have a data validation list with the options "Yes" and "No". I am trying to make it so that if in T12 I select "Yes" from the drop down list then the option "No" auto fills from the list in N12. I have tried using the formula in N12: =IF(T12="Yes", "No", "") but I get the error: "The value doesn't match the data validation restrictions defined for this cell".

 

If anyone knows a different approach for this issue. I cannot use macros/VBA as I am restricted from using them.

BI & Data Analysis
Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 17, 2023

    Noah_Zouine 

    It seems like you're trying to create a dependency between the data validation lists in cells T12 and N12 based on the selection in T12. Unfortunately, direct data validation doesn't support dynamic changes in the list based on another cell's value. However, you can achieve a similar effect using a helper column and a named range.

    Here is an alternative approach:

    1. Create a Helper Column:
      • In an empty column, let's say Column O, you can use the following formula in cell O12:

    excelCopy code

    =IF(T12="Yes", "No", "")

      • Drag this formula down for all the rows where you want the validation to be applied.
    1. Create Named Ranges:
      • Create two named ranges:
        • Name one range for "Yes" (e.g., YesList) that includes all the "Yes" values in Column O.
        • Name another range for "No" (e.g., NoList) that includes all the "No" values in Column O.
    2. Apply Data Validation in N12:
      • Go to cell N12.
      • Apply Data Validation with a formula:

    =IF(T12="Yes", YesList, NoList)

    1. This formula dynamically changes the validation list in cell N12 based on the selection in T12.

    Now, when you select "Yes" in T12, the validation list in N12 will dynamically change to show only "No" options. Adjust the cell references and ranges based on your actual data.

    Remember that this method requires an additional column (Column O in this case), but it provides a way to work around the limitation of direct dynamic data validation lists based on cell values. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources