Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing "0" from insert
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? |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "Kenny Dee" wrote in message ... 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? |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- "Kenny Dee" wrote in message ... 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? |
#4
|
|||
|
|||
Try in B1, copied down: =IF(A1="","",A1+4)
Or, if using in cond format formulas, something along the lines of: =AND(B1TODAY(),B1<"") or =AND(B1TODAY(),ISNUMBER(B1)) instead of just: =B1TODAY() (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 <atyahoo<dotcom ---- "Kenny Dee" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simultaneouly insert of rows or col. - Ajit | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |