Trying to condense text in IF formula

[Strykewulf]

I want to compile IF(AND(C12="",C13="",C14="",C15=""),XYZ,"")

Into IF(ISBLANK(C12:C15),XYZ,"")

But when I do that, it doesn't work. Is there a more efficient way to say 'If those four cells are blank, use this list, if not, leave blank"?

Also, I can't use relative references, because this formula determines B12, then B16 has to look at C16:C19. B20 looks at C20:C23, and so on...

Also, is there a way to program this into VBA? I am learning VBA right now, and have a VERY basic understanding...

[Fluff13]

Marginally shorter

Please Login or Register  to view this content.

[Pepe Le Mokko]

Perhaps =IF(COUNTBLANK(C12:C15)=ROWS(C12:C15),XYZ,"")

COUNTBLANK counts empty cells AND cells containing the null string

[Bo_Ry]

Please try

=IF(COUNTA(C12:C15),"",XYZ)

and for drag down

=IF(MOD(ROW(),4)+COUNTA(C12:C15),"","XYZ")

[Pepe Le Mokko]

COUNTA returns 1 for a cell with the null string

[Bo_Ry]

For null string change to

=IF(MOD(ROW(),4)+COUNTIF(C12:C15,"?*"),"",XYZ)

[Strykewulf]

Fluff13's resolution works perfectly! Now on the other side of the coin, is it possible to change the other side of the =/<> to a multi-criteria poll?

Example: IF($B$12<>OR("714-16","715-16"),'LISTA',"")

I tried using OR() after the = or <>... but it only looks at the first value...

[Fluff13]

Not sure if I've understood you correctly, but try

Please Login or Register  to view this content.

[Strykewulf]

Will this make the cell True if B12 is neither 714-16, or 715-16? But if B12 is any other value, including "", then it is false?

[Strykewulf]

=IF(AND($B$16<>{"714-16","715-16"},$B$20<>{"714-16","715-16"},$B$24<>{1158,"714-8","714-16","715-8","715-16"},$J$12&$J$13&$J$14&$J$15=""),Block2_8,"")

When I enter this in Data Validation, it says "you may not use reference operators (such as unions, intersections, and ranges) or array constants for data validation criteria.

I had the following as my data validation:
=IF(AND($B$16<>"714-16",$B$16<>"715-16",$B$20<>"714-16",$B$20<>"715-16",$B$24<>1158,$B$24<>"714-8",$B$24<>"714-16",$B$24<>"715-8",$B$24<>"715-16",$J$12="",$J$13="",$J$14="",$J$15=""),Block2_8,"")

And it works just fine. Was looking for a way to shorten the code/formula a little bit. Also, anyone know why Data validation doesn't work if you type it somewhere (like Word) then copy/paste to the data validation formula line? It would save me a TON of time writing out these complex formulas if I could copy from word to past into data validation...

[Strykewulf]

New problem. Boss wants a message to pop up the first time you select "1100R" from one of the many drop downs, but if you put more than one "1100R" he doesn't want a message every time. How do I exit the Sub if the Sub has been run previously?