Conditional data validation

{ 14 Comments }

By Ashish Mathur · · · Leave a comment

Assume that in range A2:A11, cells have been validated to allow a person to enter Y or N.  In B2:B11, cells should be validated based on the entry selected by the end-user in range A2:A11.  So, if a user selected Y in cell A2, then a drop down should appear in cell B2, allowing him to choose from Delhi, Mumbai, Chennai and Kolkata.  If the user selects N in cell A2, he should be able to enter any value in cell B2.

To accomplish this, try the following procedure

1. Type Delhi, Mumbai, Chennai and Kolkata in I2:I5 and assign it a name (Ctrl+F3), say source1

2. In A2, select Y

3. In cell B2, go to Data > Validation > Allow > List > Source > =IF(A2=”Y”,source1,B2)

With Y in cell A2, B2 will show four cities in the validation drop down.  When the value in cell A2 changes to N, the user will be able to enter any value in cell B2.

Leave a Comment Cancel reply

Your email address will not be published.

CAPTCHA

*

  • In cell A1, I’m having an open date assume “11/17/13”
    In cell A2, I’m having a status assume “Pending”
    In cell A3, I’m having a closed date assume “”
    Here, I didn’t enter my closed date since the status is “Pending”. Now I need a data validation in which if the cell value of the cell A2 is “Pending”, I should not be allowed to enter the closed date in cell A3.

    For this I tried the below mentioned formula in data validation but I couldn’t get it.

    =IF(A2=”Pending”,””)

    Can I get a correct formula for this data validation?

    Reply

  • Thanks this’s working.
    I used =A2=”Closed” through which I can give a closed a date in cell A3 if the cell value A2 is “Closed”.
    Moving forward, I want that closed date should not exceed the current date & it must be a date so I I’ve given “=AND(E5=”Closed”&G5<=TODAY())" in data validation. But its not working.

    Kindly suggest me a formula.

    Reply

    • “=AND(E5=”Closed”,G5<=TODAY())"
      The above formula is working. But I need some modification in error box as follows,

      1) If the Status is not closed, it should return "Status is not closed"
      2) If the cell value is not a Date data, it should return "Enter a valid date"
      3) If the Status is closed & but if the closed date ,exceeds the current date, it should return "Closed cannot be greater then the current date"

      Can we do this?

      Reply

  • Hi,

    I have problem in case of Data Validation – Error Alert settings. In this case, if my custom formula is not fulfilled, data in cell is not accepted and Error Alert is flashing. In this Error Alert, three option Buttons appears -> Retry, Cancel & Help. I want to remove Help Button as i have my own Help Menu set for F1 Key. If someone clicks on this Help Menu, it open the window of Excel Help and then my Help key as well as other Short Keys defined fails to work. Pl. help me out how i can remove this Help Button or create my own Message Box for this Error Alert.

    Reply

  • I made a conditional dropdown that is working fine, but now I’d like to copy it down to other cells and can’t because the first reference cell is anchored. I tried unanchoring it, but I get an error that the conditional dropdown only works with anchored cells. For example, I have =if($F$8=sheet3!$a$2,B_and_B-Skincare,if(…)). I’d like to unanchor F8, but using $F8 isn’t working. Any advice? Thanks!

    Reply