IF Function or VBA to validate NOT Null

[JP2R]

Greetings all -

I hope that I post this correctly - this is my first post to the group. I've read the rules, and believe I understood.

I have an image to help explain what it is that I'm trying to attempt...(I hope it's attached)

I tried to write this as an "IF" statement, but believe it is not possible to write an IF where it states "Is Not Null" in a function, but requires a VBA.

I'm trying to write something that provides an answer to the following question(s).

If Column "AB" and "AC" are Empty and Column "AD" is less than "Y" - then Column "AG" is Empty.

If Column "AB" and "AC" are Empty and Column "AD" is greater than "Y" - then Column "AG" is 1.

If Column "AB" and "AC" are Not Null and Column "AD" is greater than "Y" - then Column "AG" is Empty.

Now as I see this I wonder if it should not be a Case Statement Instead.
I'm new to VBA and the more advanced functions - so I apologize in advance if I've missed something...

I'm using Windows XP
Excel 2007

-- Monk

[herzberg]

While it's true that isn't a "Is Not Null" thingy in the worksheet functions, there is an IsBlank alternative. Try this (lengthy) formula:

=IF(ISBLANK(AB1),IF(ISBLANK(AC1),IF(AD1<10,"","1"),"False"),IF(ISBLANK(AC1),"",IF(AD1<10,"1","")))

Put it into column AG and you should see some results whenever the values AB, AC or AD change.

You didn't mention how much you want "Y" to be, so I just let it be 10. There are 2 instances of it in the formula above, so amend them to suit your needs.

[thomaspatton]

You can try this in column G as well :

=IF(AND(AB1="",AC1="",AD1>=Y1),"1","")

[JP2R]

Thanks so much!
This produced a result when I used the cell reference...Great!

-- Monk


While it's true that isn't a "Is Not Null" thingy in the worksheet functions, there is an IsBlank alternative. Try this (lengthy) formula:

=IF(ISBLANK(AB1),IF(ISBLANK(AC1),IF(AD1<10,"","1"),"False"),IF(ISBLANK(AC1),"",IF(AD1<10,"1","")))

Put it into column AG and you should see some results whenever the values AB, AC or AD change.

You didn't mention how much you want "Y" to be, so I just let it be 10. There are 2 instances of it in the formula above, so amend them to suit your needs.

[JP2R]

I am most gracious to all the responses - this solution worked great! - Thank you all for taking the time to read and reply.

You guys are awesome!
-- Monk


Greetings all -

I hope that I post this correctly - this is my first post to the group. I've read the rules, and believe I understood.

I have an image to help explain what it is that I'm trying to attempt...(I hope it's attached)

I tried to write this as an "IF" statement, but believe it is not possible to write an IF where it states "Is Not Null" in a function, but requires a VBA.

I'm trying to write something that provides an answer to the following question(s).

If Column "AB" and "AC" are Empty and Column "AD" is less than "Y" - then Column "AG" is Empty.

If Column "AB" and "AC" are Empty and Column "AD" is greater than "Y" - then Column "AG" is 1.

If Column "AB" and "AC" are Not Null and Column "AD" is greater than "Y" - then Column "AG" is Empty.

Now as I see this I wonder if it should not be a Case Statement Instead.
I'm new to VBA and the more advanced functions - so I apologize in advance if I've missed something...

I'm using Windows XP
Excel 2007

-- Monk