#ref!

  • Thread starter Thread starter ianripping
  • Start date Start date
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?
 
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
(e-mail address removed)
 
I have tried inputting this in but I get a Type Mismatch error. Is this
because I am using Excel 97 maybe?
 
I have tried inputting this in but I get a Type Mismatch error. Is this
because I am using Excel 97 maybe?
 
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
(e-mail address removed)
 
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
 
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
(e-mail address removed)
 
Back
Top