I'm having a problem here... recently upgraded to a new version of excel
and I've been looking in all the options can't seem to find a solution.

It seems whenever I enter a formula referencing a blank cell on another
sheet, instead of displaying a blank value like on the other sheet it
shows a "0". I still want to display 0 values where appropriate, so
selecting "do not show zero values" doesn't work for me.

I just want it to show a zero in cells formated at numeric, and blank
values (nothing) in cells formatted as either general or text or
whatever.

Any help would be appreciated,

Thanks.

Chris


--
Carnadyne
------------------------------------------------------------------------
Carnadyne's Profile: http://www.excelforum.com/member.php...o&userid=27443
View this thread: http://www.excelforum.com/showthread...hreadid=469528

Generally, an IF function is used to test for blank cells and return a
blank, as such:

IF(A1="","",YourFormulaHere,0)

The double quote ("") returns a blank cell if A1 is blank.

HTH


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469528

Yeah the single quote "" doesn't get rid of the zero's, I tried. Even if
I just put a formula in sheet1 such as "=Sheet2!B1" If B1 is blank... it
will display a zero on sheet1 instead of a blank.

The actual formula I'm using is:

=IF($D$1=(CHOOSE((WEEKDAY(TODAY(),1)),"Sunday","Mo nday","Tuesday","Wednesday","Thursday","Friday","S aturday"))
& "'s Tasks:",Test!A3,"")

Notice the single quote at the end of the formula... It displays a
zero, and the destintion cell is formatted as text.

Did I misunderstand what you wrote? I'm fairly new to this stuff.

Thanks.


--
Carnadyne
------------------------------------------------------------------------
Carnadyne's Profile: http://www.excelforum.com/member.php...o&userid=27443
View this thread: http://www.excelforum.com/showthread...hreadid=469528

Carnadyne Wrote:
Yeah the single quote "" doesn't get rid of the zero's, I tried. Even if
I just put a formula in sheet1 such as "=Sheet2!B1" If B1 is blank... it
will display a zero on sheet1 instead of a blank.

The actual formula I'm using is:

=IF($D$1=(CHOOSE((WEEKDAY(TODAY(),1)),"Sunday","Mo nday","Tuesday","Wednesday","Thursday","Friday","S aturday"))
& "'s Tasks:",Test!A3,"")

Notice the single quote at the end of the formula... It displays a
zero, and the destintion cell is formatted as text.

Did I misunderstand what you wrote? I'm fairly new to this stuff.

Thanks.
First, "=Sheet2!B1" _will_ return zero if B1 is blank. However,
=IF(Sheet2!B1="","",B1) will return blank unless B1 is not blank, then
it will return whatever is in B1.

Second, I don't see a 'single quote' anywhere in your formula. A
single quote is: [ " ] not to be confused with an apostrophe: [ ' ].
A double quote is two quotes without any spaces or text between them: [
"" ] and is different from 4 apostrophes: [ ' ' ' ' ].

Your formula, as written, will return a blank when D1 does not contain
today's day. What isn't working for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469528

Add an isblank formula into your if statement.

=if(isblank(a1)=true,"",a1)


--
BlueDaze


------------------------------------------------------------------------
BlueDaze's Profile: http://www.excelforum.com/member.php...o&userid=27465
View this thread: http://www.excelforum.com/showthread...hreadid=469528