Excel, can you change how formula error display?

[rcrecelius]

I think I have seen this before but now I cant find it.
Is there a way to make formula error display as something other than #N/A?
Excel 2002

 

[gizmo1973]

You could try is error with a return value of you choice.

Regards, Phil

"If in doubt, hit it with an end user!

 

[SkipVought]

rcrecelius,
[tt]
=if(isna(A1*B1),"",A1*B1)
[/tt]


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[AnotherHiggins]

if(isna(your formula),"what you want to display",your formula)

If you want to capture all errors, then use iserror instead of isna.

John

Every generalization is false, including this one.

 

[rcrecelius]

Wow, you guys are fast!
I should have been more specific, the error is the result of a vlookup formula and I was trying to keep from having to type the lookup twice.
Heres my formula as is...
VLOOKUP(B$1&"#"&$A2,'BT L1_L5 904'!$A:$F,6,FALSE)

If I were to use iserror, I think I would have to do this...
=IF(ISERROR(VLOOKUP(B$1&"#"&$A2,'BT L1_L5 904'!$A:$F,6,FALSE)),"error",VLOOKUP(B$1&"#"&$A2,'BT L1_L5 904'!$A:$F,6,FALSE))
Is there a shorter way to write this formula?

It just occured to me that I could also copy this formula and paste it in each new sheet as it is the same every time.

 

[gizmo1973]

Not really a shorter way, bit of a pain.
I tend to write the lookup, attach the iserror and copy the lookup part back in again after the "error"
gets easoer as you use it, I use it all the time as a matter of course.


Regards, Phil

"If in doubt, hit it with an end user!

 

[AnotherHiggins]

There are times when you can head off the error.
E.g. if your formula involves division you could test the divisor first to be sure it isn't zero. Like this:
[COLOR=blue white]=if(a2-b2)=0,"",(c1-d1)/(a2-b2))[/color]

But that's about as good as it gets. With VLookup, you'll have to repeat it. As Phil points out, it gets easier the more you write this type of formula.


John

Every generalization is false, including this one.

 

[gizmo1973]

or even easoer Phat Fingers
Least you get the drift!

Regards, Phil

"If in doubt, hit it with an end user!

 

[rcrecelius]

Thanks for the input.
What prompted me to ask was that I could have sworn that I saw something in properties or options in Excel that had something to do with how errors are displayed.
Anyway, I'll just c&p my formula into each new sheet and I'll only have to change a few letters each time...which is much easier than typing the formula each time.
Thanks again.

 

[GlennUK]

I shorten things like this by using INDEX MATCH instead of VLOOKUP, and splitting the MATCH and the INDEX into separate columns, having the MATCH in a hidden column.

So I'd have:
=MATCH(B$1&"#"&$A2,'BT L1_L5 904'!$A:$A,0)

in one column, hidden,

and next door I'd have:
=IF(ISNA(cell_ref),"error",INDEX('BT L1_L5 904'!$F:$F,cell_ref))

Cheers, Glenn.