|
Custom validation
The following custom validation is not correct, anyone know what is wrong
with it? =if(K22 ="QS")=J98<=M98 Pat |
Pat,
Seeing as you don't tell us what you want, we can only guess, but maybe =if(OR(K22 ="QS",K22=J98,K22<=M98), do_something,do_something_else) -- HTH Bob Phillips "Pat" wrote in message ... The following custom validation is not correct, anyone know what is wrong with it? =if(K22 ="QS")=J98<=M98 Pat |
Hi Bob,
Sorry for the vagueness of my post. Originally the custom validation had the following formula: =J98<=M98 this prevented a greater value in J98 being entered than the value present in M98 I now require modification when occasionally K22 contains the value 'QS' I say occasionally because other values will be in K22 and this should not effect the custom validation. I have tried your suggestion and it was not accepted by the validation window. Regards Pat "Bob Phillips" wrote in message ... Pat, Seeing as you don't tell us what you want, we can only guess, but maybe =if(OR(K22 ="QS",K22=J98,K22<=M98), do_something,do_something_else) -- HTH Bob Phillips "Pat" wrote in message ... The following custom validation is not correct, anyone know what is wrong with it? =if(K22 ="QS")=J98<=M98 Pat |
Pat,
I was unclear whether by validation you meant DV or just a formula, it is clear now you meant the former, which is why it didn't work with the IF in it. Still not absolutely sure what the test is, but I think you want if the J98<=M98 test only applies if K22="QS", then =AND(K22="QS", J98<=M98) if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) Take your pick. -- HTH Bob Phillips "Pat" wrote in message ... Hi Bob, Sorry for the vagueness of my post. Originally the custom validation had the following formula: =J98<=M98 this prevented a greater value in J98 being entered than the value present in M98 I now require modification when occasionally K22 contains the value 'QS' I say occasionally because other values will be in K22 and this should not effect the custom validation. I have tried your suggestion and it was not accepted by the validation window. Regards Pat "Bob Phillips" wrote in message ... Pat, Seeing as you don't tell us what you want, we can only guess, but maybe =if(OR(K22 ="QS",K22=J98,K22<=M98), do_something,do_something_else) -- HTH Bob Phillips "Pat" wrote in message ... The following custom validation is not correct, anyone know what is wrong with it? =if(K22 ="QS")=J98<=M98 Pat |
if the J98<=M98 test only applies if K22="QS", then
=AND(K22="QS", J98<=M98) The first formula does not allow a user to enter any value if K22 contains anything other than "QS" if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) The second, does not allow greater than the value on M98 if K22 contains anything other than "QS" Its important that any value greater than M98 can be entered if K22 does not contain "QS" Also, when K22 does contain the value "QS" no data is allowed to be entered. Its necessary that data is allowed to be entered if K22 contains "QS" if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) The third, allows a value greater than the value in M98 if K22 contains "QS", but does not allow a value greater than M98 if K22 contains anything other than "QS" The third option seems to be close to what I am looking for, only it needs to be the opposite way round. I have tried to see what happens when the formula is changed, only it gives the same result. =OR(J98<=M98,K22="QS" ) Pat "Bob Phillips" wrote in message ... Pat, I was unclear whether by validation you meant DV or just a formula, it is clear now you meant the former, which is why it didn't work with the IF in it. Still not absolutely sure what the test is, but I think you want if the J98<=M98 test only applies if K22="QS", then =AND(K22="QS", J98<=M98) if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) Take your pick. -- HTH Bob Phillips "Pat" wrote in message ... Hi Bob, Sorry for the vagueness of my post. Originally the custom validation had the following formula: =J98<=M98 this prevented a greater value in J98 being entered than the value present in M98 I now require modification when occasionally K22 contains the value 'QS' I say occasionally because other values will be in K22 and this should not effect the custom validation. I have tried your suggestion and it was not accepted by the validation window. Regards Pat "Bob Phillips" wrote in message ... Pat, Seeing as you don't tell us what you want, we can only guess, but maybe =if(OR(K22 ="QS",K22=J98,K22<=M98), do_something,do_something_else) -- HTH Bob Phillips "Pat" wrote in message ... The following custom validation is not correct, anyone know what is wrong with it? =if(K22 ="QS")=J98<=M98 Pat |
are we talking
=OR(AND(K22<"QS", J98<=M98),AND(K22="QS", J98M98)) or =OR(AND(K22="QS", J98<=M98),AND(K22<"QS", J98M98)) or =OR(AND(K22<"QS", J98M98),AND(K22="QS", J98<=M98)) or do I give up? Which cell is this going in, J98 or M98, and how about laying out a decision table? -- HTH Bob Phillips "Pat" wrote in message ... if the J98<=M98 test only applies if K22="QS", then =AND(K22="QS", J98<=M98) The first formula does not allow a user to enter any value if K22 contains anything other than "QS" if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) The second, does not allow greater than the value on M98 if K22 contains anything other than "QS" Its important that any value greater than M98 can be entered if K22 does not contain "QS" Also, when K22 does contain the value "QS" no data is allowed to be entered. Its necessary that data is allowed to be entered if K22 contains "QS" if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) The third, allows a value greater than the value in M98 if K22 contains "QS", but does not allow a value greater than M98 if K22 contains anything other than "QS" The third option seems to be close to what I am looking for, only it needs to be the opposite way round. I have tried to see what happens when the formula is changed, only it gives the same result. =OR(J98<=M98,K22="QS" ) Pat "Bob Phillips" wrote in message ... Pat, I was unclear whether by validation you meant DV or just a formula, it is clear now you meant the former, which is why it didn't work with the IF in it. Still not absolutely sure what the test is, but I think you want if the J98<=M98 test only applies if K22="QS", then =AND(K22="QS", J98<=M98) if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) Take your pick. -- HTH Bob Phillips "Pat" wrote in message ... Hi Bob, Sorry for the vagueness of my post. Originally the custom validation had the following formula: =J98<=M98 this prevented a greater value in J98 being entered than the value present in M98 I now require modification when occasionally K22 contains the value 'QS' I say occasionally because other values will be in K22 and this should not effect the custom validation. I have tried your suggestion and it was not accepted by the validation window. Regards Pat "Bob Phillips" wrote in message ... Pat, Seeing as you don't tell us what you want, we can only guess, but maybe =if(OR(K22 ="QS",K22=J98,K22<=M98), do_something,do_something_else) -- HTH Bob Phillips "Pat" wrote in message ... The following custom validation is not correct, anyone know what is wrong with it? =if(K22 ="QS")=J98<=M98 Pat |
Thank you for your persistence, the following gave the closest result:
=OR(AND(K22<"QS", J98M98),AND(K22="QS", J98<=M98)) when modified it gave the result I was looking: =OR(AND(K22<"QS", J98<M98),AND(K22="QS", J98<=M98)) Which cell is this going in, J98 or M98, and how about laying out a decision table? J98, what is a decision table and would it be of help now? regards Pat "Bob Phillips" wrote in message .. . are we talking =OR(AND(K22<"QS", J98<=M98),AND(K22="QS", J98M98)) or =OR(AND(K22="QS", J98<=M98),AND(K22<"QS", J98M98)) or =OR(AND(K22<"QS", J98M98),AND(K22="QS", J98<=M98)) or do I give up? Which cell is this going in, J98 or M98, and how about laying out a decision table? -- HTH Bob Phillips "Pat" wrote in message ... if the J98<=M98 test only applies if K22="QS", then =AND(K22="QS", J98<=M98) The first formula does not allow a user to enter any value if K22 contains anything other than "QS" if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) The second, does not allow greater than the value on M98 if K22 contains anything other than "QS" Its important that any value greater than M98 can be entered if K22 does not contain "QS" Also, when K22 does contain the value "QS" no data is allowed to be entered. Its necessary that data is allowed to be entered if K22 contains "QS" if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) The third, allows a value greater than the value in M98 if K22 contains "QS", but does not allow a value greater than M98 if K22 contains anything other than "QS" The third option seems to be close to what I am looking for, only it needs to be the opposite way round. I have tried to see what happens when the formula is changed, only it gives the same result. =OR(J98<=M98,K22="QS" ) Pat "Bob Phillips" wrote in message ... Pat, I was unclear whether by validation you meant DV or just a formula, it is clear now you meant the former, which is why it didn't work with the IF in it. Still not absolutely sure what the test is, but I think you want if the J98<=M98 test only applies if K22="QS", then =AND(K22="QS", J98<=M98) if the J98<=M98 test only applies if K22 does not ="QS", then =AND(K22<"QS", J98<=M98) if either the J98<=M98 test applies or if K22 ="QS", then =OR(K22="QS", J98<=M98) Take your pick. -- HTH Bob Phillips "Pat" wrote in message ... Hi Bob, Sorry for the vagueness of my post. Originally the custom validation had the following formula: =J98<=M98 this prevented a greater value in J98 being entered than the value present in M98 I now require modification when occasionally K22 contains the value 'QS' I say occasionally because other values will be in K22 and this should not effect the custom validation. I have tried your suggestion and it was not accepted by the validation window. Regards Pat "Bob Phillips" wrote in message ... Pat, Seeing as you don't tell us what you want, we can only guess, but maybe =if(OR(K22 ="QS",K22=J98,K22<=M98), do_something,do_something_else) -- HTH Bob Phillips "Pat" wrote in message ... The following custom validation is not correct, anyone know what is wrong with it? =if(K22 ="QS")=J98<=M98 Pat |
Pat,
A decision table is often use to map out a number of different condition combinations. For instance, you would put all the different conditions that affect K22 across the top, then all the conditions that affect J98/M98 down the side, and in the various intersection points you state what result you want in that condition. Often, some conditions will give the same result as another, or are an impossible condition, but by mapping it out, you get a very clear picture of what to do. In this case, as you have now solved it, it would be of no help. The biggest problem I have had with this thread (no big deal, but for future information), is in understanding exactly what was allowable and in what circumstances. This is why I took some many guesses at it, it wasn't clear at the start that there were 2 combinations that were permissible. Anyway, you are sorted now, so we have been successful. Regards Bob "Pat" wrote in message ... Thank you for your persistence, the following gave the closest result: =OR(AND(K22<"QS", J98M98),AND(K22="QS", J98<=M98)) when modified it gave the result I was looking: =OR(AND(K22<"QS", J98<M98),AND(K22="QS", J98<=M98)) Which cell is this going in, J98 or M98, and how about laying out a decision table? J98, what is a decision table and would it be of help now? regards Pat |
| All times are GMT +1. The time now is 04:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com