SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-803849%22%20slang%3D%22zh-CN%22%3ESet%20data%20validation%20with%20both%20both%20specified%20text%20and%20drop%20down%20list%20in%20the%20not-for-set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803849%22%20slang%3D%22zh-CN%22%3E%3CP%3EHi%2C%20there.%20I%20need%20to%20set%20a%20data%20validation%20in%20a%20cell%20so%20show%20a%20specified%20text%20automatic%20inally%20in%20condition%201%2C%20and%20show%20a%20drop%20down%20List%20in%20condition%202%2C%20is%20it%20sie%3F%3C%2FP%3E%3CP%3EI've%20made%20the%20data%20for%20example%2C%20many-how%20for%20any%20advice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-803849%22%20slang%3D%22zh-CN%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-803911%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20data%20validation%20with%20both%20specified%20text%20and%20drop%20down%20list%20in%20different%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391931%22%20target%3D%22_blank%22%3E%40jie_z%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInitially%2C%20you%20need%20this%20formula%20in%20the%20Status%20column%3A%3C%2FP%3E%3CPRE%3E%3DIF(A2%26gt%3B%24F%241%2C%22not%20started%20yet%22%2CIF(B2%26lt%3B%24F%241%2C%22finished%22%2C%22SelectStatus%22))%3C%2FPRE%3E%3CP%3EIf%20the%20two%20conditions%20failed%2C%20the%20formula%20will%20show%20(SelectStatus)%20which%20will%20be%20the%20source%20reference%20of%20the%20drop-down%20list%20in%20the%20data%20validation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20notice%20that%20when%20the%20cell%20contains%20(SelectStatus)%2C%20the%20drop-down%20list%20will%20be%20available.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126909i783ACB4FEDC31134%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Select%20Status.png%22%20title%3D%22Select%20Status.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20restriction%20to%20this%20solution%20is%20that%20when%20you%20select%20the%20status%20from%20the%20drop-down%20list%2C%20you%20can't%20select%20it%20again%20until%20you%20apply%20the%20formula%20on%20the%20range%20again%20or%20type%20(SelectStatus)%20in%20the%20cell%20manually!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20that%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806216%22%20slang%3D%22zh-CN%22%3ERe%3A%20Set%20data%20validation%20with%20both-time%20both-time%20text%20and%20drop%20down%20list%20in-nos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806216%22%20slang%3D%22zh-CN%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3EThank%20you%20so%20much!%20This%20real%20ly%20ly%20dosly%20dosly%20dosly%20dosly%20dosi%20dos%20i%20dos%20to%20my%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
jie_z
New Contributor

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.

2 Replies
Solution

@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.

Select Status.png

 

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.

@Haytham Amairah Thank you so much!This really does much help to my work.