Add Second Function To Validation Formula

[swordswinger710]

I'm trying to get a validation formula to work with Cell I2. It needs to check the number entered to make sure there is no dash, which is working with this formula:

=IF(ISNUMBER(SEARCH("-",I2)),FALSE, TRUE)

I'm trying to expand that formula to also check if there's a dash in Cell C2, and not allow the value to be entered in Cell I2 if there is a value without one.

So basically, it needs to make sure there is no dash in the value entered into Cell I2, and that if Cell C2 has a value, it includes a dash. (If there is no value in Cell C2, then no dash doesn't matter.)

How would I be able to make this work?

[DGagnon]

Try this out

=IF(ISNUMBER(SEARCH("-",I2)),FALSE,IF(OR(ISNUMBER(SEARCH("-",C2)),C2=""),TRUE,FALSE))

[swordswinger710]

That is fantastic, thank you so much!

[swordswinger710]

Would you have any idea how to modify that formula so I could also use it in Cell I9 to do this:

Only allow an entry containing a '-2', and only if Cell C1 contains a '-1'?

[DGagnon]

=if(isnumber(search("-2",i2)),false,if(or(isnumber(search("-1",c2)),c2=""),true,false))

[swordswinger710]

That doesn't seem to be doing it - I changed it to this:

=IF(ISNUMBER(SEARCH("-2",I9)),FALSE,IF(OR(ISNUMBER(SEARCH("-1",C2)),C2=""),TRUE,FALSE))

..because I9 is the cell I'm using it in, but I'm able to enter any value except for a '-2' value. The checking for '-1' in Cell C2 works, though...

[DGagnon]

Try this

=IF(ISNUMBER(SEARCH("-2",I9)),IF(OR(ISNUMBER(SEARCH("-1",C1)),C1=""),TRUE,FALSE),FALSE)

[swordswinger710]

Okay, upon changing the C1's to C2's in the formula, that works when Cell C2 contains a value. But it allows Cell I9 to contain anything when C2 is empty.

[DGagnon]

Give this a shot... if not could you please attach a sample book with a couple conditions to test?

=IF(OR(ISNUMBER(SEARCH("-1",C1)),C1=""),IF(ISNUMBER(SEARCH("-2",I9)),TRUE,FALSE),FALSE)

[swordswinger710]

Yeah, that's still not working, and I think you're right, we may not be understanding each other fully here.

Please feel free to check the attachment, that should help.

[DGagnon]

i was allowing for a blank cell in C2, try this

IF(ISNUMBER(SEARCH("-1",C2)),IF(ISNUMBER(SEARCH("-2",I9)),TRUE,FALSE),FALSE)

[swordswinger710]

Hmm, almost there, but I'm still having one more issue - try entering 12345-1 into Cell C2, and then enter 12345-2 into Cell I9.

[DGagnon]

should that not be allowed?

[swordswinger710]

Yes, that should be allowed. Cell I9 may only contain any value (ending with a -2, of course) if Cell C2 has data with a -1.

[DGagnon]

That is what it is doing, it seems to be working for me.

[swordswinger710]

So when you enter 12345-2 into Cell I9, it lets you do it?

[DGagnon]

yes, are you using the last formula i provided?

=IF(ISNUMBER(SEARCH("-1",C2)),IF(ISNUMBER(SEARCH("-2",I9)),TRUE,FALSE),FALSE)

[swordswinger710]

I thought I was, but after pasting your last formula into Data Validation, that part is working. Are you able to enter 12345-2 into Cell I9 when Cell C2 is empty? Because I am.. and I shouldn't be able to.

[DGagnon]

Try this..

=IF(AND(ISNUMBER(SEARCH("-1",C2)),C2<>""),IF(ISNUMBER(SEARCH("-2",I9)),TRUE,FALSE),FALSE)

[swordswinger710]

I can still enter anything I like into Cell I9 when Cell C2 is empty.

[swordswinger710]

Which is not a good thing.

[DGagnon]

make sure you have "Ignore Blank" unchecked

[swordswinger710]

That did it! Thank you very much. One last thought - do you know if there's any way to specify that the value must end with a -2 only, and not a -22 or anything like that? In my case, it's highly unlikely that will happen, but I'm just wondering if there's some kind of formula code for that.

[DGagnon]

yes, you just need to modify your formula to use right, instead of search.

something like this (note i changed the formula up too)

=IF(AND(RIGHT(C2,2)="-1",RIGHT(I9,2)="-2"),TRUE,FALSE)

[swordswinger710]

That is lovely, thank you so much!

[DGagnon]

no problem at all