View Full Version : Setcell to blank if results of a formula are null, based on checkbox conditition

08-08-2013, 07:56 AM
Hi All,

Hopefully I can do this without a macro. I've searched the internet with no luck so far.

I have a cell where I build up the cell contents based on values in other cells and want it to be blank if there's no values. It's built based on a checkbox conditional.

So I have two tabs, RuleSet and ACL. On RuleSet, any given row has four cells (a, b, c, d) with values. On ACL, I want to do a check of cell A and leave the cell on ACL blank if the result is blank. But being as the cell in RuleSet is filled in based on a formula, even if it looks blank, it's not. Is there an easy way to set cell a4 on ACL to blank?

I've attached an example.... you'll see that I have the two checkboxes at the top. The rows below are built out or not depending on whether a box is checked or not. So the "PROD Access" box is not checked, resulting in cell a8 through d8 looking like they are blank. But on the ACL tab, I have the text prefix displayed even though the cell looks blank. How can I end up with cell A4 on the ACL tab really being blank in this condition?

I've left the cells unprotected although they are normally locked and protected to prevent inadvertent overwriting.


Kenneth Hobs
08-08-2013, 09:21 AM
Use cell="" rather that IsBlank(cell).

The forum software is acting up so I will quote the formula.

=IF(RuleSet!A8="","","permit "&IF(OR(RuleSet!D8="",RuleSet!C8="",AND(RuleSet!C8="any",RuleSet!D8="")),"ip",IF(RuleSet!C8="any","tcp",RuleSet!C8))&" any "&IF(RuleSet!B8="","host ","")&RuleSet!A8&IF(NOT(RuleSet!B8="")," "&RuleSet!B8,"")&IF(AND(RuleSet!C8<>"icmp",NOT(RuleSet!C8=""),NOT(ISBLANK(RuleSet!D8)))," eq "&RuleSet!D8,"")&IF(AND(RuleSet!C8="any",NOT(RuleSet!D8="")),CHAR(10)&"permit udp any "&IF(RuleSet!B8="","host ","")&RuleSet!A8&IF(NOT(RuleSet!B8="")," "&RuleSet!B8,"")&" eq "&RuleSet!D8,""))

Kenneth Hobs
08-08-2013, 09:22 AM

08-08-2013, 10:02 AM
Thanks Kenneth,

That worked perfectly. I was pulling my hair out with your original post with the motie and &QUOTs all over.