• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation Formulas

dparteka

dparteka

Member
I'm trying to use the custom data validation function in Excel and am looking for a working formula, the validation formula would exist in B1... here’s what I want to do in plain words...


if A1 is text then accept entries in B1 of only "Accept" or "Reject" otherwise if A1 is a number then accept entries in B1 of only numbers.


Click menu items... Data > Validation > under "Allow" choose "Custom"
 
Luke, thank-you for your response... your formula works great for the Accept/Reject part... but not for the number part, when A1 is a number then B1 should accept only numbers entries.


Here are the rules:

When A1 is text then B1 only accepts "Accept" or "Reject" entries

When A1 is a number then B1 only accepts number entries
 
Hi, dparteka!

Check this:

=OR(AND(ISNUMBER(A1),ISNUMBER(B1)),AND(NOT(ISNUMBER(A1)),OR(B1="Accept";B1="Reject")))

Regards!
 
Hey SirJB7... that's impressive, you're a magician, I've been trying to do that for two days, what a relief... thank you so much
 
dparteka,

Glad you found a solution that works. I'm not sure why my formula wasn't working for you, as it works fine in my workbook.
 
@dparteka

Hi!

Sorry for not having checked before Luke M's solution, as you went on posting I assumed the previous post didn't work, but Luke M's one it works perfectly for me. I tried it and it does the job.

Regards!


@Luke M

Hi!

Your formula for B1 validation works fine in my test workbook too.

As I told dparteka, didn't analyze what was posted first.

Regards!
 
Luke M & SirJB7... okay I ran a few tests and now know how I concluded that Luke M's formula wasn't working. I have macros and shortcut keys setup for entering the "Accept" & "Reject" text, when I use the macro to make the entry in B1 it accepts it when it should not... if I actually type the word in the field then the validation works and the error alert message is displayed, this condition holds true for both Luke M & SirJB7 formulas. I've made some chances in my macros to alleviate this problem.


You're both awesome and I can't emphasize enough my appreciation for your help... thanks guys
 
Ah, thanks for clearing that up. Yep, macros and a user pasting from another cell with ignore/override data validation, respectively. =(

Sometimes useful, sometimes not.
 
You must log in or register to reply here.
Back
Top