SOLVED
Home

Excel - Data Validation

%3CLINGO-SUB%20id%3D%22lingo-sub-1184671%22%20slang%3D%22en-US%22%3EExcel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EIn%20Excel%20-%20I%20have%20a%20simple%20calculation%20for%20Cell%20K25%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3D((J22*K24)%2B1)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20But%20I%20want%20to%20enforce%20a%20rule%20on%20the%20RESULT%3A%20Cell%20K25%20must%20Also%20be%20%26lt%3B%3D%20C10%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Can%20you%20help%20me%20with%20the%20required%20formula%2FData%20Validation%20for%20this%20cell%3F%20I%20can%E2%80%99t%20get%20my%20Data%20Validation%20error%20message%20to%20appear%3F%20My%20DV%20input%20screen%20is%20in%20image%20file%20below.%20Thanks!%20Steve-SDC%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1184671%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184708%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMIN(J22*K24%2B1%2CC10)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184725%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184725%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20use%20Data%20validation%20then%3A%3C%2FP%3E%3COL%3E%3CLI%3ESet%20Data%20Validation%20to%20%22less%20than%20or%20equal%20to%22%3C%2FLI%3E%3CLI%3EDecide%20whether%20or%20not%20to%20%22ignore%20blank%22%20by%20checking%20or%20unchecking%20box%3C%2FLI%3E%3CLI%3EIn%20Error%20Alert%20tab%2C%20ensure%20that%20%22Show%20error%20alert%20after%20invalid....%22%20is%20checked%3COL%3E%3CLI%3EInsert%20an%20error%20message%20if%20you%20wish.%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185258%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Sergio%2C%20but%20that%20didn't%20do%20it%20-%20the%20recommended%20syntax%20meant%20it%20just%20used%20the%20value%20for%20cell%20C10%20since%20it%20was%20the%20MIN%20(lower)%20of%20the%202.%20I%20need%20to%20Check%2Fvalidate%20that%20my%20calculation%20is%20Less%20Than%20C10%20-%20by%20sending%20an%20error%20message%2C%20Tried%20to%20use%20Data%20Validation%20but%20can't%20get%20it%20to%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185317%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20-%20I%20had%20already%20tried%20that%2C%20but%20does%20not%20work.%20When%20the%26nbsp%3B%20cell%20that%20is%20being%20calculated%20exceeds%20the%20value%20of%20cell%20C10%2C%20it%20is%20still%20populated.%20I%20want%20it%20to%20generate%20my%20error%20message%20in%20the%20event%20that%20the%20result%20is%20not%20Less%20Than%20value%20contained%20in%20(Max)%20cell%20C10.%20Further%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185374%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185374%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20to%20recalculate%20the%20cell%20with%20data%20validation%20after%20you%20set%20the%20data%20validation.%20If%20you%20set%20the%20data%20validation%20after%20you%20already%20calculated%20the%20cell%2C%20then%20it%20will%20not%20show%20an%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185537%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185537%22%20slang%3D%22en-US%22%3ESergio%2C%20do%20you%20mean%20use%20that%20syntax%20in%20the%20Data%20Validation%20box%3F%20If%20I%20use%20it%20in%20cells%20J22%20and%20K24%20I%20can%20no%20longer%20enter%20them%20manually.%20I%20want%20the%20User%20to%20enter%20them%20manually%20-%20and%20simply%20check%20after%20they%20have%20been%20entered%20by%20using%20K25%20for%20multiplying%20them%2C%20result%20of%20K25%20must%20be%20%26lt%3B%3D%20C10.%20%3F%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185445%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185445%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20and%20if%20J22%20and%20K24%20are%20entered%20manually%2C%20you%20may%20apply%20to%20each%20of%20above%20cells%20data%20validation%20with%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(%24J%2422*%24K%2424%2B1)%20%26lt%3B%3DC10%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185550%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20mean%20such%20data%20validation%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20444px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172334i71D576B8094465C6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Efor%20both%20cells.%20User%20could%20enter%20to%20these%20cell%20manually%20any%20values%2C%20and%20they%20will%20receive%20alert%20if%20formula%20calculation%20result%20exceed%20value%20of%20the%20cell%20C10.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185561%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185561%22%20slang%3D%22en-US%22%3EThanks%20-%20tried%20that.%20Still%20did%20not%20work.%20I%20want%20the%20User%20to%20manually%20enter%20J22%20and%20K24.%20The%20Result%20is%20calculated%20to%20K25.%20But%20I%20want%20an%20error%20message%20(or%20dis-allow%20K25%20to%20be%20populated)%20IF%20K25%20is%20%26gt%3B%20C10.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185579%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185579%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20assume%20we%20have%20correct%20result%20at%20the%20beginning%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20254px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172336iD3133C0E43C4CDF4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20we%20are%20truing%20to%20enter%2055%20into%20K24%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20410px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172337i5B910AAC5BA77E57%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWith%20this%20we%20have%20two%20options%20-%20enter%20correct%20value%20to%20K24%20or%20cancel%20and%20return%20back%20to%20initial%20data.%3C%2FP%3E%0A%3CP%3EOr%20I%20misunderstood%20and%20you%20consider%20another%20scenario%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185592%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergio%20-%20Thanks%20Again!%20I%20selected%20both%20J22%20%26amp%3B%20K24%2C%20then%20applied%20the%20formula%20to%20both%2C%20but%20as%20example%20shows%2C%20it%20still%20allowed%20K25%20to%20exceed%20value%20of%20C10%3F%20See%20attached%20image%20which%20shows%20the%20Cells%20and%20DV%20box%3C%2FP%3E%3CP%3ESteve%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185599%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185599%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20the%20example%20you%20have%20shown%20matches%20mine%2C%20but%20I%20am%20not%20getting%20the%20error%20flagged%20properly%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185612%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can't%20reproduce%20that%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20424px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172341iEC47D114288A3F30%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached%20file%20with%20the%20sample.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185660%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20Da%20Man!!%20Thanks%20so%20much.%20My%20Problem%3A%20I%20selected%20both%20J22%20and%20K24%20and%20Jointly%20set%20the%20Data%20Validation%20parameters.%20Once%20that%20I%20Cleared%20DV%2C%20then%20re-set%20the%20DV%20Parameters%20individually%20for%20each%20cell%2C%20it%20works!%20I%20am%20(obviously)%20not%20that%20well-versed%20with%20formulas%20-%20I%20really%20appreciate%20your%20patience%20and%20sending%20an%20Excel%20file%20as%20an%20example.%20THANKS%20AGAIN!!%3C%2FP%3E%3CP%3ESteve%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185715%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564226%22%20target%3D%22_blank%22%3E%40Steve-SDC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESteve%2C%20great%20to%20know%20you%20sorted%20this%20out%2C%20glad%20to%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

In Excel - I have a simple calculation for Cell K25:              =((J22*K24)+1)         But I want to enforce a rule on the RESULT: Cell K25 must Also be <= C10      Can you help me with the required formula/Data Validation for this cell? I can’t get my Data Validation error message to appear? My DV input screen is in image file below. Thanks! Steve-SDC

15 Replies
Highlighted

@Steve-SDC 

You may use

=MIN(J22*K24+1,C10)
Highlighted

Hello @Steve-SDC,

 

If you wish to use Data validation then:

  1. Set Data Validation to "less than or equal to"
  2. Decide whether or not to "ignore blank" by checking or unchecking box
  3. In Error Alert tab, ensure that "Show error alert after invalid...." is checked
    1. Insert an error message if you wish. 
Highlighted

@Sergei Baklan 

Thanks Sergio, but that didn't do it - the recommended syntax meant it just used the value for cell C10 since it was the MIN (lower) of the 2. I need to Check/validate that my calculation is Less Than C10 - by sending an error message, Tried to use Data Validation but can't get it to work?

Highlighted

@PReagan 

Thanks - I had already tried that, but does not work. When the  cell that is being calculated exceeds the value of cell C10, it is still populated. I want it to generate my error message in the event that the result is not Less Than value contained in (Max) cell C10. Further ideas?

Highlighted

Hello @Steve-SDC,

 

Try to recalculate the cell with data validation after you set the data validation. If you set the data validation after you already calculated the cell, then it will not show an error.

Highlighted

@Steve-SDC 

As variant and if J22 and K24 are entered manually, you may apply to each of above cells data validation with the formula

=($J$22*$K$24+1) <=C10

 

Highlighted
Sergio, do you mean use that syntax in the Data Validation box? If I use it in cells J22 and K24 I can no longer enter them manually. I want the User to enter them manually - and simply check after they have been entered by using K25 for multiplying them, result of K25 must be <= C10. ??
Highlighted

@Steve-SDC 

I mean such data validation

image.png

for both cells. User could enter to these cell manually any values, and they will receive alert if formula calculation result exceed value of the cell C10.

Highlighted
Thanks - tried that. Still did not work. I want the User to manually enter J22 and K24. The Result is calculated to K25. But I want an error message (or dis-allow K25 to be populated) IF K25 is > C10.
Highlighted

@Steve-SDC 

Let assume we have correct result at the beginning

image.png

Now we are truing to enter 55 into K24

image.png

With this we have two options - enter correct value to K24 or cancel and return back to initial data.

Or I misunderstood and you consider another scenario?

Highlighted

@Sergei Baklan 

Sergio - Thanks Again! I selected both J22 & K24, then applied the formula to both, but as example shows, it still allowed K25 to exceed value of C10? See attached image which shows the Cells and DV box

Steve

Highlighted

Yes, the example you have shown matches mine, but I am not getting the error flagged properly @Sergei Baklan 

Highlighted
Solution

@Steve-SDC 

I can't reproduce that

image.png

Please check attached file with the sample.

Highlighted

@Sergei Baklan 

You Da Man!! Thanks so much. My Problem: I selected both J22 and K24 and Jointly set the Data Validation parameters. Once that I Cleared DV, then re-set the DV Parameters individually for each cell, it works! I am (obviously) not that well-versed with formulas - I really appreciate your patience and sending an Excel file as an example. THANKS AGAIN!!

Steve

Highlighted

@Steve-SDC 

Steve, great to know you sorted this out, glad to help.