Custom validation

[Pat]

The following custom validation is not correct, anyone know what is wrong
with it?

=if(K22 ="QS")=J98<=M98

Pat

 

[Bob Phillips]

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)

 

[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]

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.

 

[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]

are we talking

=OR(AND(K22<>"QS", J98<=M98),AND(K22="QS", J98>M98))

or

=OR(AND(K22="QS", J98<=M98),AND(K22<>"QS", J98>M98))

or

=OR(AND(K22<>"QS", J98>M98),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?

 

[Pat]

Thank you for your persistence, the following gave the closest result:

=OR(AND(K22<>"QS", J98>M98),AND(K22="QS", J98<=M98))

when modified it gave the result I was looking:

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]

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