How to capture #REF! in Excel?

Question

I would like to know how to capture the #REF! error in Excel.

I am able to catch error in vba with the following code :

    If WorksheetFunction.IsError(ActiveCell) Then
         If ActiveCell.Value = CVErr(xlErrRef) Then
              ActiveCell.Value = "Error"
         End If
   End If

I would like to write the same function in Excel Formula Bar. Any ideas how to do it?

Thanks.

You may need to combine the `IsError` formula with the `IsRef` formula. See my suggestion below. — David Zemens, Apr 19 '13 at 13:50

unangelic · Answer 1 · 2013-04-19T14:11:54.000

3

You can use ISERROR in the formula bar and check if true or false

this works to identify only isref errors =IFERROR(IF(ERROR.TYPE(A1)=4,"Ref Error",A1),"All GOOD")

edited Apr 19 '13 at 14:11

answered Apr 19 '13 at 13:37

unangelic

75
7

Well, ISERROR() will catch all the errors. I would like to catch on #REF! – Kiran Apr 19 '13 at 13:38
Try to do something with this =ERROR.TYPE(#REF!) – unangelic Apr 19 '13 at 13:54
ok here you go --- =IF(ERROR.TYPE(A1)=4,"Error","All good") .. if error type is #ref then "error" else "all good" – unangelic Apr 19 '13 at 14:00
Except Error.Type will return N/A if there is no error in the cell. – Denise Skidmore Apr 19 '13 at 14:05
Another wrinkle for OP -- and perhaps this doesn't matter -- is that formula can contain multiple error conditions, but the cell value will only display the first error encountered. E.g., `=SheetDoesNotExist!A1+badname` will give a `#Ref!` error, while `=badName+SheetDoesNotExist!A1` will give a `#Name?` error. – David Zemens Apr 19 '13 at 14:56

David Zemens · Answer 2 · 2013-04-19T17:28:01.963

2

Note that the cell will only return the first error encountered when evaluating a formula, so if a cell has a #Name? error that occurs before a #Ref! error, there is no way to trap the potential #Ref! error.

Maybe something like:

=AND(ISREF(A1),ISERR(A1))

First test whether the cell contains a REFERENCE and then check to see if it also contains an error.

I revise to use IsErr instead of IsError to ignore N/A type errors.

edited Apr 19 '13 at 17:28

answered Apr 19 '13 at 13:40

David Zemens

53,033
11
81
130

1

You can have ref and a different kind of error in the same formula. Consider `=B5+badname` – Denise Skidmore Apr 19 '13 at 14:04

score 2 · Answer 3 · answered Apr 19 '13 at 14:02

2

=IF(ISERROR(A1),ERROR.TYPE(#REF!)=ERROR.TYPE(A1),FALSE)

Returns true on #ref, false on all other errors or no error.

answered Apr 19 '13 at 14:02

Denise Skidmore

2,286
22
51

You could use 4 in place of ERROR.TYPE(#REF!), but I think this makes it clearer what you're doing. – Denise Skidmore Apr 19 '13 at 14:06

score 1 · Answer 4 · answered Apr 19 '13 at 13:39

1

You can use the formula IF(ISERROR(ActiveCell),"Error")

This will however, catch all other errors as well.

answered Apr 19 '13 at 13:39

John Willemse

6,608
7
31
45

Yes, You are right. However, I want to catch only the #REF! error – Kiran Apr 19 '13 at 13:39

How to capture #REF! in Excel?

4 Answers4

Linked