Empty Cells, Spaces, Cond Format?

[Ken]

Excel 2000 ... I run into this often & usually come up
with work-arounds, but I have decided to ask the wizards
of this board ... What should I do?

I am always having a problem with formulas when I base
them on an empty cell & then I subsequently enter a
formula to the empty cell.

Everything works fine (sample, not actual, below)

Cell A1 = Empty Cell
Cell B1 = Empty Cell
Cell C1 = Formula & Conditional Formatting (based on
Cell B1 being empty)

Now I put a formula in Cell B1 depentent on A1.

Cell B1 formula ... =if(isblank(a1),"",x)

Issue ... the formula in B1 is now causing Conditional
format formula to fail because Cell B1 is no longer a
BLANK cell.

What adjustments should I make to my Cond Formatting
Formulas so they will recognize a Cell containing a
formula (or formula producing a "space" value) the same
way it recognized the cell when it was empty???

Thanks ... Kha

 

[Bernie Deitrick]

Ken,

In your CF formula, use B1="" to check for blank. If your formula in B1
returns "" or if B1 is truly blank, that will always be true.

HTH,
Bernie
MS Excel MVP

 

[Anon y mous]

have you considered using "If(iserror" so you can leave the cell with a
blank value instead of having an error show up?

I do this all the time so columns of totals, averages and standard
deviations do not come up error filled when not all the cells are used
within a range...


example:
IF(ISERROR(A1/B1),"",A1/B1)

Paul

 

[Anon y mous]

you may have to nest the two types together to get your desired effect
(between the 'isblank' and 'iserror', depending upon the mathematical
expressions you are using.

Good luck
Paul