# notblank?

#### nelsok

##### Board Regular
I dont think there is a funtion similar to isblank for testing if a cell isnotblank.

I can not use if in this case.

Can I multiply isblank()*-1 to get the opposite?

along with this question is.... can i use countif to do this? If so, does countif count as an if statment? the reason I ask is becuase I am at the max for nested ifs

any other suggestions?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### VoG

##### Legend
Does this work?

If Not Application.Isblank(Range("A1")) Then

#### HalfAce

##### MrExcel MVP
can i use countif to do this?
You can use CountA to see if (for example) B1 is not blank.
=COUNTA(B1,"<>""")
This will return a zero if it's blank and a 1 if it's not.
It will not count as one of your allowed 7 nested ifs.

Does this help?

#### nelsok

##### Board Regular
Does this work?

If Not Application.Isblank(Range("A1")) Then

maybe you could tell me.

=IF(AND(OR(Q\$6=1,Q\$6=2,Q\$6=3),notblank(H9)),VLOOKUP(\$N9&LOOKUP(Q\$6,\$A\$2:\$A\$7,\$B\$2:\$B\$7),'Upload Pivot'!\$A\$5:\$O\$700,7,FALSE)*Q\$5),IF(OR(Q6=4,Q6=5),"if the value is 4 or 5 do this",IF(OR(Q6=8,Q6=9,Q6=10,Q6=11,Q6=11,Q6=12),"do this if it is a prod request",IF(OR(Q6=12,Q6=13,Q6=14),"if it is an export allocation do this",LOOKUP(Q6,P602:P616,Q602:Q616)*Q5))))

#### nelsok

##### Board Regular

can i use countif to do this?
You can use CountA to see if (for example) B1 is not blank.
=COUNTA(B1,"<>""")
This will return a zero if it's blank and a 1 if it's not.
It will not count as one of your allowed 7 nested ifs.

Does this help?

So i could use this in my and as COUNTA(B1,"<>""")=1

but i think there may be problem becuase the cell value is "" as given by another formula

#### Doug.T

##### Board Regular
would these work for you?

=(ISBLANK(a1)=FALSE)
or
=(LEN(A1)>0 )

nelsok

or

H9 <>""

#### VoG

##### Legend
I'm confused (no difference there then!) . Are you looking for a formulas or VBA?

#### nelsok

##### Board Regular
I am trying to use formulas

#### Peter_SSs

##### MrExcel MVP, Moderator
So, will this do it?

=IF(AND(OR(Q\$6=1,Q\$6=2,Q\$6=3),H9<>""),VLOOKUP(\$N9&LOOKUP(Q\$6,\$A\$2:\$A\$7,\$B\$2:\$B\$7),'Upload Pivot'!\$A\$5:\$O\$700,7,FALSE)*Q\$5),IF(OR(Q6=4,Q6=5),"if the value is 4 or 5 do this",IF(OR(Q6=8,Q6=9,Q6=10,Q6=11,Q6=11,Q6=12),"do this if it is a prod request",IF(OR(Q6=12,Q6=13,Q6=14),"if it is an export allocation do this",LOOKUP(Q6,P602:P616,Q602:Q616)*Q5))))

Replies
12
Views
168
Replies
6
Views
78
Replies
3
Views
69
Replies
4
Views
60
Replies
10
Views
301