Empty Cells, Spaces, Cond Format?
 

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

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

"Ken" wrote in message
...
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

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


"Ken" wrote in message
...
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

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


"Ken" wrote in message
...
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