Forum Discussion

jie_z's avatar
jie_z
Copper Contributor
Aug 14, 2019
Solved

Set data validation with both specified text and drop down list in different conditions

Hi,there.I need to set a data validation in a cell so it shows a specified text automatically in condition 1, and shows a drop down list in condition 2,is it possible?

I've attached the data for example, many thanks for any advice.

excel
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Aug 14, 2019

    jie_z

     

    Hi,

     

    Initially, you need this formula in the Status column:

    =IF(A2>$F$1,"not started yet",IF(B2<$F$1,"finished","SelectStatus"))

    If the two conditions failed, the formula will show (SelectStatus) which will be the source reference of the drop-down list in the data validation.

     

    You will notice that when the cell contains (SelectStatus), the drop-down list will be available.

     

    One restriction to this solution is that when you select the status from the drop-down list, you can't select it again until you apply the formula on the range again or type (SelectStatus) in the cell manually!

     

    Please find that in the attached file.

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Aug 14, 2019

    jie_z

     

    Hi,

     

    Initially, you need this formula in the Status column:

    =IF(A2>$F$1,"not started yet",IF(B2<$F$1,"finished","SelectStatus"))

    If the two conditions failed, the formula will show (SelectStatus) which will be the source reference of the drop-down list in the data validation.

     

    You will notice that when the cell contains (SelectStatus), the drop-down list will be available.

     

    One restriction to this solution is that when you select the status from the drop-down list, you can't select it again until you apply the formula on the range again or type (SelectStatus) in the cell manually!

     

    Please find that in the attached file.

Resources