Odd formatting issue
 

I'm sure this is a no-brainer...

I have a bunch of read-only columns way over on the right of a sheet.
I use formulas to copy that into the visible portion on the left. One
of the formulas is...

=IF(CB20=1,CI20,"")

This works in many cases, but if CI20 is "" (ISBLANK returns TRUE)
then the value is "0". Why is this, and how do I get a blank here
instead?

Maury

Odd formatting issue
 

=IF(OR(CB20<1,ISBLANK(CI20),"",CI20)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Maury Markowitz" wrote in message
...
I'm sure this is a no-brainer...

I have a bunch of read-only columns way over on the right of a sheet.
I use formulas to copy that into the visible portion on the left. One
of the formulas is...

=IF(CB20=1,CI20,"")

This works in many cases, but if CI20 is "" (ISBLANK returns TRUE)
then the value is "0". Why is this, and how do I get a blank here
instead?

Maury

Odd formatting issue
 

try

IF(CB20=1, IF(CI20="","",C120),"")


"Maury Markowitz" wrote in message
...
I'm sure this is a no-brainer...

I have a bunch of read-only columns way over on the right of a sheet.
I use formulas to copy that into the visible portion on the left. One
of the formulas is...

=IF(CB20=1,CI20,"")

This works in many cases, but if CI20 is "" (ISBLANK returns TRUE)
then the value is "0". Why is this, and how do I get a blank here
instead?

Maury

Odd formatting issue
 

try

=IF(CB20=1,IF(CI20="","",C120),"")


"Maury Markowitz" wrote in message
...
I'm sure this is a no-brainer...

I have a bunch of read-only columns way over on the right of a sheet.
I use formulas to copy that into the visible portion on the left. One
of the formulas is...

=IF(CB20=1,CI20,"")

This works in many cases, but if CI20 is "" (ISBLANK returns TRUE)
then the value is "0". Why is this, and how do I get a blank here
instead?

Maury

Odd formatting issue
 

I realize I can program around this with a ISBLANK, but there's hundreds of
examples like this and it will be an extremely time consuming and bug-prone
process to find them all.

So my question is really "why is this happening". If I put "=C120" into the
cell then it correctly puts in "". But if I use an IF to select one of the
two values, I get a "0". Is this a side-effect of the IF?

If so, is there some other way to choose between the contents of two cells
that doesn't have this side-effect?

Maury

Odd formatting issue
 

interesting - I can't replicate this issue

if I use
=C120 i get whatever is in C120, or nothing if its null
similarly
=IF(C120=1,C120,"")
shows nothing unless C120 contains 1

using Excels 2003 and 2007

"Maury Markowitz" wrote in
message ...
I realize I can program around this with a ISBLANK, but there's hundreds
of
examples like this and it will be an extremely time consuming and
bug-prone
process to find them all.

So my question is really "why is this happening". If I put "=C120" into
the
cell then it correctly puts in "". But if I use an IF to select one of the
two values, I get a "0". Is this a side-effect of the IF?

If so, is there some other way to choose between the contents of two cells
that doesn't have this side-effect?

Maury