Excel formula cell hiding

[sampath]

Hello,

Once put formula in particular cell, Which is shown like "#VALUE!, when source cell is empty. how is possible to avoid this.

Kindly help for the same.

Thank with regards,
Sampath.S

 

[Asheesh]

Sampath - sample file could have been of much help..nevertheless...

If you are using 2007 or above...you can use iferror(formula,"")

if it 2003 or below, you can use..if(iserror(formula),"",formula)

 

[Smallman]

Hi

If you are using Excel 07 or later:

=iferror(YourFormula,"")

Excel 03 or earler

=IF(ISERROR(YourFormula),"",YourFormula)

Take care

Smallman

Edit - posted within a minute of the above
No visibility.

 

[Khalid NGO]

Or you can use CF to hide errors.
Select your range:
CF Formula : iserror(first cell)
Format : Format the Font and Background with same color.

Regards,

 

[Somendra Misra]

Hi Sampath,

Although I am sure all the above suggestions will work, I would ask you to upload a sample file with your formula so that we can have a better look of it.

Regards,

 

[NARAYANK991]

Hi Sampath ,

Going by the recent discussions about the use of IFERROR , I would suggest this alternative :

If you know that the source of the error is the source (!) cell being empty or blank , check for that ; using an IF statement around your formula should suffice :

=IF(ISBLANK(source cell) , "" , Your formula)

or

=IF(source cell = "" , "" , Your formula)

Narayan

 

[sampath]

Sampath - sample file could have been of much help..nevertheless...

If you are using 2007 or above...you can use iferror(formula,"")

if it 2003 or below, you can use..if(iserror(formula),"",formula)

Hello Asheesh,

I have attached the screen shot for sample.

Regards,
Sampath.S

 

[Asheesh]

As per the screen shot above..incorporating the formula in the above solutions..

=IFERROR(RIGHT(A1,LEN(A1)-FIND("SE",A1)),"")

=IF(ISERROR(RIGHT(A1,LEN(A1)-FIND("SE",A1))),"",RIGHT(A1,LEN(A1)-FIND("SE",A1)))

=IF(ISBLANK(A1), "", RIGHT(A1,LEN(A1)-FIND("SE",A1)))

=IF(A1 = "", "", RIGHT(A1,LEN(A1)-FIND("SE",A1)))