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

Gajendran Yadhav

Board Regular
Joined
Sep 8, 2023
Messages
51
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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)

1701753509896.png



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
 
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:

Excel Formula:
=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
NOPQ
1Monetary ComponentBy / AgainstClassificationData Validation formula
20FIRVERDADERO
35FIRFALSO
4abcFIRFALSO
50CriminalVERDADERO
65CriminalFALSO
7abcCriminalFALSO
80CDVERDADERO
95CDVERDADERO
10abcCDFALSO
110WCVERDADERO
125WCVERDADERO
13abcWCFALSO
140FALSO
155FALSO
16abcFALSO
Hoja1
Cell Formulas
RangeFormula
Q2:Q16Q2=AND(P2<>"",IF(OR(P2 = "FIR", P2 = "Criminal"), N2 = 0, ISNUMBER(N2)))
Hi Felixstraube,
Thanks for your suggestion to check if column P is not empty.... it works great.

Kudoos man !!

I tried using LEN function instead and it turned out to work as well.

The formula I tried is
Excel Formula:
=(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)))*AND(LEN(P5)<>0)

Thanks for your extended support.
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Thanks for the feedback. Yes LEN will work as well.
But you are using wrong the AND function. In your formula it isn't doing anything the real AND operation comes from the * before the AND.
The proper usage of AND would be:

Excel Formula:
=AND(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)),LEN(P5)<>0)

Or without AND function you could write:

Excel Formula:
=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)
 
Upvote 0
Thanks for the feedback. Yes LEN will work as well.
But you are using wrong the AND function. In your formula it isn't doing anything the real AND operation comes from the * before the AND.
The proper usage of AND would be:

Excel Formula:
=AND(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)),LEN(P5)<>0)

Or without AND function you could write:

Excel Formula:
=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)
Dear,

Thanks for your guidance over the usage of AND function. and btw, is there any possibility to add another control where if P5="CD", N5>=2

I tried my best, but couldn't figure to concatenate multiple functions into one.

I will go with this formula which you suggested...
Excel Formula:
=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)
 
Upvote 0
Thanks for the feedback. Yes LEN will work as well.
But you are using wrong the AND function. In your formula it isn't doing anything the real AND operation comes from the * before the AND.
The proper usage of AND would be:

Excel Formula:
=AND(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5)),LEN(P5)<>0)

Or without AND function you could write:

Excel Formula:
=IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0)
Dear Felix,

Please ignore my previous reply... I was not able to edit as time expired.

Thanks a lot for your guidance over my concern and in the usage of AND function.

And by the way, I tried adding another condition that if P5="CD", N5=>2. -- Accidentally I got it worked by using the below formula..

I will go with this formula which you suggested...
Excel Formula:
=(IF(P5="CD",N5>=2,(IF(OR(P5="FIR",P5="Criminal"),N5=0,ISNUMBER(N5))*(LEN(P5)<>0))))<>0))))

Any suggestions or comments is much obliged. TIA
 
Upvote 0
With you new formula (which has to many closing parenthesis) you won't be checking first if you have entered something in column P

In your case, as I understand it you want first to check
1. if there is something entered in Classification
2. Monetary always has to be a number (not text)
3. if Classificaction is FIR or Criminal, monetary only can be 0
4. if Classification is CD, monetary should be equal or greater than 2
5. if Classification is any other value, monetary can be any value

In logic this can be written like this:
Code:
Validation = 
    (Class <> "")
    AND 
    (Monet is number)
    (
        ((Class = "FIR") AND (Monet = 0))
        OR
        ((Class = "Criminal") AND (Monet = 0))
        OR
        ((Class = "CD") AND (Monet >= 2))
        OR
        ((Class <> "FIR") AND (Class <> "Criminal") AND (Class <> "CD") AND (Monet Is number))
    )

for it to work in excel you will have to use * as AND operators and + as OR operator like this

Excel Formula:
= 
    (P2 <> "")
    *
    ISNUMBER(N2)
    *
    (
        ((P2 = "FIR") * (N2 = 0))
        +
        ((P2 = "Criminal") * (N2 = 0))
        +
        ((P2 = "CD") * (N2 >= 2))
        +
        ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD"))
    )

Rewrite it to a one line formula:
Excel Formula:
= (P2 <> "") * ISNUMBER(N2) * (((P2 = "FIR") * (N2 = 0)) + ((P2 = "Criminal") * (N2 = 0)) + ((P2 = "CD") * (N2 >= 2)) + ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD")))

Or you can write it with the AND and OR functions:
Excel Formula:
 =
AND(
    P2<>""
    ,
    ISNUMBER(N2)
    ,
    OR(
        AND(P2="FIR", N2=0),
        AND(P2="Criminal", N2=0),
        AND(P2="CD", N2>=2),
        AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")
    )
)

The same in one line:

Excel Formula:
 = AND(P2<>"", ISNUMBER(N2), OR(AND(P2="FIR", N2=0), AND(P2="Criminal", N2=0), AND(P2="CD", N2>=2), AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")))
 
Upvote 0
With you new formula (which has to many closing parenthesis) you won't be checking first if you have entered something in column P

In your case, as I understand it you want first to check
1. if there is something entered in Classification
2. Monetary always has to be a number (not text)
3. if Classificaction is FIR or Criminal, monetary only can be 0
4. if Classification is CD, monetary should be equal or greater than 2
5. if Classification is any other value, monetary can be any value

In logic this can be written like this:
Code:
Validation =
    (Class <> "")
    AND
    (Monet is number)
    (
        ((Class = "FIR") AND (Monet = 0))
        OR
        ((Class = "Criminal") AND (Monet = 0))
        OR
        ((Class = "CD") AND (Monet >= 2))
        OR
        ((Class <> "FIR") AND (Class <> "Criminal") AND (Class <> "CD") AND (Monet Is number))
    )

for it to work in excel you will have to use * as AND operators and + as OR operator like this

Excel Formula:
=
    (P2 <> "")
    *
    ISNUMBER(N2)
    *
    (
        ((P2 = "FIR") * (N2 = 0))
        +
        ((P2 = "Criminal") * (N2 = 0))
        +
        ((P2 = "CD") * (N2 >= 2))
        +
        ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD"))
    )

Rewrite it to a one line formula:
Excel Formula:
= (P2 <> "") * ISNUMBER(N2) * (((P2 = "FIR") * (N2 = 0)) + ((P2 = "Criminal") * (N2 = 0)) + ((P2 = "CD") * (N2 >= 2)) + ((P2 <> "FIR") * (P2 <> "Criminal") * (P2 <> "CD")))

Or you can write it with the AND and OR functions:
Excel Formula:
 =
AND(
    P2<>""
    ,
    ISNUMBER(N2)
    ,
    OR(
        AND(P2="FIR", N2=0),
        AND(P2="Criminal", N2=0),
        AND(P2="CD", N2>=2),
        AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")
    )
)

The same in one line:

Excel Formula:
 = AND(P2<>"", ISNUMBER(N2), OR(AND(P2="FIR", N2=0), AND(P2="Criminal", N2=0), AND(P2="CD", N2>=2), AND(P2<>"FIR", P2<>"Criminal", P2<>"CD")))
that's real nice of you...
🙏
I learnt something new...
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top