Conversation Some Excel help - Data validation using name ranges - force option if "False" in Excel
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504018#M30019
<P>Hi I was looking for some help please. I have used a range </P><P> </P><P>=IF($N$28="True",$Q$3:$Q$4,IF($N$28="False",$Q$4))</P><P> </P><P>this works fine, in that the O3 & O4 cells are just Yes/No selections.. </P><P> </P><P>this is being driven from </P><P> </P><P>=IF($C$22>=590,"True","False")</P><P> </P><P>in my data validation I just use =<rangename></P><P> </P><P>however is there a way so that it forces the data validation to switch to "No" when No is the only option available.. By this I mean if they have populated a calculation sheet and being able to select the Yes option, if they then change the number and it falls below 590.. The field will still populate as "Yes" even though this isnt a valid option any longer... When you click the drop down it will only have "No" as the option, but is there a way to force it to switch to No if its below that 590 range??</P><P> </P><P>hope this makes sense... </P>Tue, 30 Apr 2019 13:38:30 GMTOwenO2019-04-30T13:38:30ZSome Excel help - Data validation using name ranges - force option if "False"
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504018#M30019
<P>Hi I was looking for some help please. I have used a range </P><P> </P><P>=IF($N$28="True",$Q$3:$Q$4,IF($N$28="False",$Q$4))</P><P> </P><P>this works fine, in that the O3 & O4 cells are just Yes/No selections.. </P><P> </P><P>this is being driven from </P><P> </P><P>=IF($C$22>=590,"True","False")</P><P> </P><P>in my data validation I just use =<rangename></P><P> </P><P>however is there a way so that it forces the data validation to switch to "No" when No is the only option available.. By this I mean if they have populated a calculation sheet and being able to select the Yes option, if they then change the number and it falls below 590.. The field will still populate as "Yes" even though this isnt a valid option any longer... When you click the drop down it will only have "No" as the option, but is there a way to force it to switch to No if its below that 590 range??</P><P> </P><P>hope this makes sense... </P>Tue, 30 Apr 2019 13:38:30 GMThttps://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504018#M30019OwenO2019-04-30T13:38:30ZRe: Some Excel help - Data validation using name ranges - force option if "False"
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504473#M30022
<P>can mark this as resolved.. had to use a VBA script which is something i just learned today</P><P> </P><P>for anyone it may potentially help who googled and found this however I've added what I used above</P><P> </P><P>you go into VBA (Alt+F11), choose the worksheet you want</P><P> </P><P>click the "change" option on the right side</P><P> </P><P>and add the code below:</P><P>If Target.Address = "$C$4" Then<BR />Range("E28").Value = "Please Select..."</P><P>End If</P><P> </P><P>(change the cell values to match the ones you need..)</P>Tue, 30 Apr 2019 15:14:20 GMThttps://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504473#M30022OwenO2019-04-30T15:14:20ZRe: Some Excel help - Data validation using name ranges - force option if "False"
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504517#M30024
<P><LI-USER uid="330526"></LI-USER> </P><P><SPAN>If I have understood your question correctly, the answer is that there is no way of forcing an input cell to switch to "No" if C22 is below that 590 range? It is possible to build the references to the data so that C22 is tested and if it is <590 the "Yes" is simply ignored and "No" is used</SPAN></P><P><SPAN>= IF(C22<590, "No", UserInput)</SPAN></P><P> </P><P><SPAN>I would also observe that you appear to be going round in circles using "True"/"False" and "Yes"/"No" to represent the Boolean values TRUE/FALSE [or, alternatively, 1/0]. Using Boolean values your first formula reduces to</SPAN></P><P><SPAN>= IF($N$28, $Q$3:$Q$4, $Q$4)</SPAN></P><P><SPAN>whilst the next one is</SPAN></P><P><SPAN>= $C$22>=590</SPAN></P><P> </P><P><SPAN>If you prefer 0/1 to FALSE/TRUE then the functions N() and SIGN() each convert Booleans to integers which allows custom number formatting.</SPAN></P>Tue, 30 Apr 2019 15:23:05 GMThttps://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504517#M30024Peter Bartholomew2019-04-30T15:23:05ZRe: Some Excel help - Data validation using name ranges - force option if "False"
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504529#M30025
<P><LI-USER uid="214174"></LI-USER> </P><P> </P><P>thanks for the reply, the reason I done the true and false is because if its 590 or over I want the user to have the ability to select Yes or No, but if under 590 then the cell to automatically update to No</P><P> </P><P>is this possible with the formula you referenced do you think?</P><P> </P><P> </P>Tue, 30 Apr 2019 15:25:36 GMThttps://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504529#M30025OwenO2019-04-30T15:25:36ZRe: Some Excel help - Data validation using name ranges - force option if "False"
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504531#M30026
<P><LI-USER uid="330526"></LI-USER> </P><P>Agreed, a VBA solution responding to an event will do it!</P>Tue, 30 Apr 2019 15:25:58 GMThttps://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504531#M30026Peter Bartholomew2019-04-30T15:25:58ZRe: Some Excel help - Data validation using name ranges - force option if "False"
https://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504773#M30031
<P><LI-USER uid="330526"></LI-USER> </P><P>Many things are possible, that does not necessarily mean they are a good idea. It is only too easy to get caught out by one's own 'cleverness' at a later date! Using Booleans rather than strings probably is a good idea but the number formatting within conditional formats is a step too far.</P>Tue, 30 Apr 2019 16:08:54 GMThttps://techcommunity.microsoft.com/t5/excel/some-excel-help-data-validation-using-name-ranges-force-option/m-p/504773#M30031Peter Bartholomew2019-04-30T16:08:54Z