Removing "0" from insert

[Kenny Dee]

I'm using this formula in one of my cells =Sheet2!E2 but if there is no info
type in from that sheet then my return input is "0" how do I remove the "0"
from ever appearing, just a blank would be great. I've saw a posting for
ADDING cells and if a number is not inserted then the return would be 0. and
they fixed the problem with this type of formula =IF(A1 + B1 = 0,"",A1 +
B1). How would I uses the with the formula that i'm using from another
sheet, as mention above?

[mudraker]

=if(Sheet2!E2=0,"",Sheet2!E2)

[Max]

2 ways to try ..

a. Switch off zeros display in the sheet via:
Tools > Options > View tab > Uncheck "Zero values" > OK

b. Instead of : =Sheet2!B1, use : =IF(Sheet2!B1="","",Sheet2!B1)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Kenny Dee" <[email protected]> wrote in message
news:[email protected]...
> I'm using this formula in one of my cells =Sheet2!E2 but if there is no
info
> type in from that sheet then my return input is "0" how do I remove the
"0"
> from ever appearing, just a blank would be great. I've saw a posting for
> ADDING cells and if a number is not inserted then the return would be 0.
and
> they fixed the problem with this type of formula =IF(A1 + B1 = 0,"",A1 +
> B1). How would I uses the with the formula that i'm using from another
> sheet, as mention above?
>
>

[Kenny Dee]

Ok this somewhat works i guess, but what if the cell is a DATE FORMAT. I
still get a date enter into a cell which has no date to refer to. For an
exsample:

Cell A1 I enter and date and in cell B1 i enter this formula =A1+4
Once a date is enter into A1 04/12/05, in cell A2 and date is entered as
04/16/05.

But if no date is enter in A2 then B2 enters a default date. (Which I don't
want) just a black cell would be fine.

"Max" wrote:

> 2 ways to try ..
>
> a. Switch off zeros display in the sheet via:
> Tools > Options > View tab > Uncheck "Zero values" > OK
>
> b. Instead of : =Sheet2!B1, use : =IF(Sheet2!B1="","",Sheet2!B1)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Kenny Dee" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using this formula in one of my cells =Sheet2!E2 but if there is no
> info
> > type in from that sheet then my return input is "0" how do I remove the
> "0"
> > from ever appearing, just a blank would be great. I've saw a posting for
> > ADDING cells and if a number is not inserted then the return would be 0.
> and
> > they fixed the problem with this type of formula =IF(A1 + B1 = 0,"",A1 +
> > B1). How would I uses the with the formula that i'm using from another
> > sheet, as mention above?
> >
> >
>
>
>

[pikapika13]

did you mean "blank" cell instead of "black."
Anyways, can you say that if cell a1 is blank, give me a blank?
=IF(ISBLANK(A1),"",A1+4).

Or do you want it to recognize if it is a date first because a1 could possibly be populated with non-date data?

[Max]

Try in B1, copied down: =IF(A1="","",A1+4)

Or, if using in cond format formulas,
something along the lines of:

=AND(B1>TODAY(),B1<>"")
or
=AND(B1>TODAY(),ISNUMBER(B1))

instead of just: =B1>TODAY()

(Above assumes the cond format is to be triggered
if the date entered / evaluated in col B is a future date)

The additional conditions:

B1<>""
ISNUMBER(B1)

will help ensure that the evaluated condition
for empty cells in col B remain FALSE
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Kenny Dee" <[email protected]> wrote in message
news:[email protected]...
> Ok this somewhat works i guess, but what if the cell is a DATE FORMAT. I
> still get a date enter into a cell which has no date to refer to. For an
> exsample:
>
> Cell A1 I enter and date and in cell B1 i enter this formula =A1+4
> Once a date is enter into A1 04/12/05, in cell A2 and date is entered as
> 04/16/05.
>
> But if no date is enter in A2 then B2 enters a default date. (Which I
don't
> want) just a black cell would be fine.