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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies