#ref!

I

ianripping

#REF! appears in many of my formula cells at the moment which isn't a
problem. I wondered if this could be replaces with a value like 0.
Maybe with one single formula or macro which works for the entire
sheet?

Any ideas?
 
N

Nick Hodge

Ian

Most functions that may return an error value should be 'wrapped' in one of
the error functions. These are ISNA, ISERR and ISERROR.

ISNA traps #N/A! errors
ISERR traps all errors except #N\A!
ISERROR traps all errors

In a VLOOKUP for example you could use

=IF(ISERROR(VLOOKUP(Ref,Range,Offset,FALSE)),"",VLOOKUP(Ref,Range,Offset,FAL
SE))

This would put nothing ("") in the cell if the VLOOKUP produced an error.
If the VLOOKUP was OK it would do the VLOOKUP function.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
I

ianripping

I have tried inputting this in but I get a Type Mismatch error. Is this
because I am using Excel 97 maybe?
 
I

ianripping

I have tried inputting this in but I get a Type Mismatch error. Is this
because I am using Excel 97 maybe?
 
N

Nick Hodge

Ian

If you are getting a type mismatch error you are putting this in code. This
will not work with the route I have given. You will have to adjust the
formulas manually.

If you want a code solution then post what you have already and someone will
take a stab

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
I

ianripping

Well if all my cells are saying #REF!, then do i put this forula in?

=IF(ISERROR(VLOOKUP(#REF!)),"0",VLOOKUP(#REF!))

so that a 0 appears instead
 
N

Nick Hodge

Ian

No

You will have to manually change the underlying formula to incorporate the
error functions, there is no automatic way of doing this automatically
without writing code.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top