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

Highlighted

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