How to hide #REF! error via MACRO

[puneet_pr]

I am working in excel sheet and in many places #REF! error has come. because many cells are blank but in future i enter some value. i want to show blank or 0 in the place of #REF! error. With the help of MACRO.

friends is it possible if yes please give me the solution. Its urgent.

 

[Andrew Poulsom]

Pleas post a sample formula that returns #REF!.

 

[Cindy Ellis]

Hello,
This is via a formula, not a macro, and assumes that you are using Excel 2007 or later. You can use the "IFERROR" function, with basic structure

=IFERROR(formula,"")

Hope that helps,

 

[puneet_pr]

Pleas post a sample formula that returns #REF!.

This is the formula.


=GETPIVOTDATA("YTD Figures",Pivot_Budget_V3_2011_V1_2012!$A$6,"WBS L2",D269,"Heads","09 AI")

 

[Jaafar Tribak]

=IFERROR(GETPIVOTDATA("YTD Figures",Pivot_Budget_V3_2011_V1_2012!$A$6,"WBS L2",D269,"Heads","09 AI"),"")

 

[puneet_pr]

Thanks Sir... its working.

 

[Jaafar Tribak]

While on this subject, does anyone know how the result of a formula resulting in an error could be hidden using Conditional Formating ?

I thought this would work but it doesn't :

CF Formula :
=ISERROR(A1)

then Format >Number >Custom> ;;;

 

[Andrew Poulsom]

While on this subject, does anyone know how the result of a formula resulting in an error could be hidden using Conditional Formating ?

I thought this would work but it doesn't :

CF Formula :
=ISERROR(A1)

then Format >Number >Custom> ;;;

You can't hide an error with that number format. You can use a white font.

 

[Jaafar Tribak]

You can't hide an error with that number format. You can use a white font.

Thanks for confirming this.