Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ken
 
Posts: n/a
Default 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


  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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




  #3   Report Post  
Anon y mous
 
Posts: n/a
Default

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




  #4   Report Post  
Anon y mous
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trendlines to ignore empty cells Hoochi Coochi Man Charts and Charting in Excel 7 January 14th 05 02:31 PM
Protected cells -automatically format to a different color Fred Evans Excel Discussion (Misc queries) 9 December 3rd 04 01:59 PM
empty cells in ranges UniDave Excel Discussion (Misc queries) 2 November 26th 04 03:18 AM


All times are GMT +1. The time now is 09:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"