To accept manual data or return a default value based on condition.

[Gajendran Yadhav]

Friends,

I'm working on a worksheet where I've a plenty of data to be validated.

I'm having the column N for entering the monetary value ranging from "0" to actual value.

If column P is equal to "FIR" or "Criminal", then column N should accept only "0" as its value.
if column P is not equal to "FIR" or "Criminal", then it can accept any value greater than "0"

As of now I'm using the Conditional Formatting to highlight in Red (as error) if the condition is satisfied.
=IF(OR(P5="FIR",P5="CRIMINAL")*AND(N5<>0),TRUE,FALSE)

Row 5 is the first row of the worksheet. Rows 1 - 4 has some headers and table heads.

A simple Data Validation formula would suffice my need. I'm not much of a techie to use VBA Codes...

Any help is much appreciated. TIA

 

[Special-K99]

That's not the correct way to use AND(). You can simply change AND(N5<>0) to (N5<>0) since you have no other parameter to AND() it with.
As far as Data Validation goes shouldn't this just be

=AND(N5<>0,OR(P5="FIR",P5="CRIMINAL"))

 

[felixstraube]

I believe you are looking for this?:

Libro1
	N	O	P	Q
1	Monetary Component	By / Against	Classification	Data Validation formula
2	0,00		FIR	1
3	5,00		FIR	0
4	0,00		Criminal	1
5	5,00		Criminal	0
6	0,00		CD	1
7	5,00		CD	1
8	0,00		WC	1
9	5,00		WC	1
Hoja1

Cell Formulas
Range	Formula
Q2:Q9	Q2	=(N2=0)*((P2="FIR")+(P2="Criminal"))+(P2<>"FIR")*(P2<>"Criminal")

 

[felixstraube]

or with AND and OR functions:

Libro1
	N	O	P	Q
1	Monetary Component	By / Against	Classification	Data Validation formula
2	0,00		FIR	VERDADERO
3	5,00		FIR	FALSO
4	0,00		Criminal	VERDADERO
5	5,00		Criminal	FALSO
6	0,00		CD	VERDADERO
7	5,00		CD	VERDADERO
8	0,00		WC	VERDADERO
9	5,00		WC	VERDADERO
Hoja1

Cell Formulas
Range	Formula
Q2:Q9	Q2	=OR(AND(N2=0,OR(P2="FIR",P2="Criminal")),AND(P2<>"FIR",P2<>"Criminal"))

 

[Gajendran Yadhav]

That's not the correct way to use AND(). You can simply change AND(N5<>0) to (N5<>0) since you have no other parameter to AND() it with.
As far as Data Validation goes shouldn't this just be

=AND(N5<>0,OR(P5="FIR",P5="CRIMINAL"))

Dear Special-K99,

Initially I tried with this formula for it being short...unfortunately, it didn't work.

Column N should accept value as 0 only when column P is either FIR or Criminal.
For other values of P, N can be anything inclusive of 0.

 

[Gajendran Yadhav]

or with AND and OR functions:

Libro1
	N	O	P	Q
1	Monetary Component	By / Against	Classification	Data Validation formula
2	0,00		FIR	VERDADERO
3	5,00		FIR	FALSO
4	0,00		Criminal	VERDADERO
5	5,00		Criminal	FALSO
6	0,00		CD	VERDADERO
7	5,00		CD	VERDADERO
8	0,00		WC	VERDADERO
9	5,00		WC	VERDADERO
Hoja1

Cell Formulas
Range	Formula
Q2:Q9	Q2	=OR(AND(N2=0,OR(P2="FIR",P2="Criminal")),AND(P2<>"FIR",P2<>"Criminal"))

Dear Felixstraube,

Thanks for giving your opinion.
I'm looking to validate the column "N" itself.
You have given for validating whether it is true or false.

The actual expectation is that column N should accept its value as 0 if the column P is either "FIR" or "Criminal".
for other values of column P, column N can accept any value inclusive of 0.

 

[Gajendran Yadhav]

Dear Special-K99 & Felixstraube,

by reading our forums of various topics and sorting to my needs,
I came up with this formula and it works.

=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))

I have also added the custom error message to pop-up on validation failure.

Thus the problem solved.
-----------------------------------------------------------------------------------------

and in continuation, I got another query.
Since column N is filled first and the column P, Excel accepts any value in column N first.
say if i enter 1 in N5 and subsequently enter FIR or Criminal in P5, nothing happens.

any possible solution to sort this out..??

I cannot interchange the columns such that column N goes next to column P.

further, column O is a list function and column P is dependent list of column O.

can column P have two data validation ?
I'm thinking of to have, column P should accept the entry only if column N is blank (empty).

or column N should accept any entry only if column P is filled with its data.


any other solution to prevent such entry will be helpful.

as of now I'm using Conditional Formatting option to highlight the both the cells (N & P) in RED denoting an error.
but no control over the data entry.

Thanks.

 

[felixstraube]

Thanks for giving your opinion.
I'm looking to validate the column "N" itself.
You have given for validating whether it is true or false.

The actual expectation is that column N should accept its value as 0 if the column P is either "FIR" or "Criminal".
for other values of column P, column N can accept any value inclusive of 0.

I provided the formula you should use in your data validation. I wrote it in the column Q just so you could see it. Use the formula in Q2 in you data validation for N2 and drag down. It should work as expected.

 

[Gajendran Yadhav]

I provided the formula you should use in your data validation. I wrote it in the column Q just so you could see it. Use the formula in Q2 in you data validation for N2 and drag down. It should work as expected.

Thanks dear. Anyways, the formula which I've mentioned is working fine for me... I think I would leave it as it is.

Do you see any additional advantage in using this formula which you suggested..??
I'll keep learning.

and I'll be much obliged if you could help with the other query as well....

TIA

 

[felixstraube]

Either formula will work just fine. Yours also checks that it is a number so it wont allow text to be entered.

To check for data in column P try this:

=AND(P2 <> "", IF(OR(P2 = "FIR", P2 = "Criminal"), N2 = 0, ISNUMBER(N2)))

And you could show error message like "Please entere a classification first. If classification is either FIR or Criminal, monetary component should be zero."

Libro1
	N	O	P	Q
1	Monetary Component	By / Against	Classification	Data Validation formula
2	0		FIR	VERDADERO
3	5		FIR	FALSO
4	abc		FIR	FALSO
5	0		Criminal	VERDADERO
6	5		Criminal	FALSO
7	abc		Criminal	FALSO
8	0		CD	VERDADERO
9	5		CD	VERDADERO
10	abc		CD	FALSO
11	0		WC	VERDADERO
12	5		WC	VERDADERO
13	abc		WC	FALSO
14	0			FALSO
15	5			FALSO
16	abc			FALSO
Hoja1

Cell Formulas
Range	Formula
Q2:Q16	Q2	=AND(P2<>"",IF(OR(P2 = "FIR", P2 = "Criminal"), N2 = 0, ISNUMBER(N2)))