Data Validation multiple

%3CLINGO-SUB%20id%3D%22lingo-sub-1487235%22%20slang%3D%22en-US%22%3EData%20Validation%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1487235%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20sheet%20that%20has%20a%20drop-down%20to%20select%20%22Annual%22%20or%20%22Semi-annual%22.%20The%26nbsp%3Bfollowing%26nbsp%3Brow%20contains%20a%20data%20validation%20drop-down%20to%20select%20the%20month%20for%20%22Annual%22%2C%20and%20the%20row%20below%20that%20contains%20the%20same.%3C%2FP%3E%3CTABLE%20width%3D%22356%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22127%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110%22%3EFrequency%3C%2FTD%3E%3CTD%20width%3D%22119%22%3ESemi-annual%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMonth%20Assessed%3C%2FTD%3E%3CTD%3EDecember%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2nd%20Month%20Assessed%3C%2FTD%3E%3CTD%3EJune%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20not%20only%20conceal%20the%202nd%20Month%20assessed%2C%20which%20I%20have%20done%20with%20conditional%26nbsp%3Bformatting%2C%20but%20I%20want%20to%20prevent%20entry%20into%20the%20cell%20with%20the%20month%20IF%20%22Annual%22%20is%20selected.%26nbsp%3B%20The%20worksheet%20is%20protected.%26nbsp%3B%20Is%20there%20a%20way%20to%20do%20this%20without%20VBA%3F%26nbsp%3B%20Is%20there%20a%20way%20to%20use%20both%20a%20Validation%20list%20and%20a%20custome%20Validation%20as%20well%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1487235%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1488006%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1488006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709359%22%20target%3D%22_blank%22%3E%40Mariegb%3C%2FA%3E%26nbsp%3BAssuming%20the%20cell%20with%20the%20drop-down%20is%20in%20C1%2C%20try%20the%20following%20DV%20rule%20in%20C3%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DC1%3D%22Semi-annual%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIt%20should%20only%20allow%20entry%20of%20data%20in%20C3%20if%20the%20value%20in%20C1%20equals%20%22Semi-annual%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1490673%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20the%26nbsp%3Bsuggestion%2C%20but%20the%20problem%20is%20that%20the%20cell%20contains%20a%20drop%20down%20DV%20list.%26nbsp%3B%20It%20will%20let%20me%20do%20one%20or%20the%20other%2C%20but%20not%20both.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1491103%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709359%22%20target%3D%22_blank%22%3E%40Mariegb%3C%2FA%3E%26nbsp%3BThen%20you%20can%20perhaps%20use%20a%20solution%20like%20in%20the%20attached%20file.%20The%20DV%20rule%20for%20the%202nd%20month%20refers%20to%20a%20list%20that%20starts%20in%20E2%20(%3D%24E%242%23).%20In%20turn%2C%20E2%20contains%20a%20formula%20that%20creates%20an%20empty%20list%20if%20C1%20contains%20%22Semi-annual%22%20or%20a%20full%20list%20of%20months%20if%20C1%20contains%20%22Semi.annual%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20if%20the%202nd%20month%20is%20always%206%20months%20after%20the%20first%20in%20a%20semi-annual%20cycle%2C%20you%20could%20consider%20to%20use%20a%20calculation%20that%20adds%206%20months%20to%20the%20first%20month%20if%20the%20frequency%20equals%20%22Semi-annual%22.%20Just%20an%20idea.%20Have%20given%20an%20example%20of%20that%20as%20well%20in%20the%20attached%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have sheet that has a drop-down to select "Annual" or "Semi-annual". The following row contains a data validation drop-down to select the month for "Annual", and the row below that contains the same.

 FrequencySemi-annual
 Month AssessedDecember
 2nd Month AssessedJune

 

I want to not only conceal the 2nd Month assessed, which I have done with conditional formatting, but I want to prevent entry into the cell with the month IF "Annual" is selected.  The worksheet is protected.  Is there a way to do this without VBA?  Is there a way to use both a Validation list and a custome Validation as well?

 

 

3 Replies
Highlighted

@Mariegb Assuming the cell with the drop-down is in C1, try the following DV rule in C3

=C1="Semi-annual"

It should only allow entry of data in C3 if the value in C1 equals "Semi-annual".

 

Highlighted

@Riny_van_Eekelen  Thanks for the suggestion, but the problem is that the cell contains a drop down DV list.  It will let me do one or the other, but not both.

 

Highlighted

@Mariegb Then you can perhaps use a solution like in the attached file. The DV rule for the 2nd month refers to a list that starts in E2 (=$E$2#). In turn, E2 contains a formula that creates an empty list if C1 contains "Semi-annual" or a full list of months if C1 contains "Semi.annual".

 

Alternatively, if the 2nd month is always 6 months after the first in a semi-annual cycle, you could consider to use a calculation that adds 6 months to the first month if the frequency equals "Semi-annual". Just an idea. Have given an example of that as well in the attached workbook.