replace error with blank

  • Thread starter Thread starter chegel
  • Start date Start date
C

chegel

I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells with a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
 
Does not work, circular ref error, and at least 1 cell has a 0
--
chris


T. Valko said:
Try these:

A1: =IF(B1=0,"",B1)
B1: =IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


chegel said:
I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells with
a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
Click to expand...
Click to expand...
 
Try this small event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:B1")
Set ra = Range("A1")
Set rb = Range("B1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Intersect(Target, ra) Is Nothing Then
ra.Value = rb.Value
Else
rb.Value = ra.Value
End If
Application.EnableEvents = True
End Sub

If either A1 or B1 is changed, the other will mimic the change.
--
Gary''s Student - gsnu200758


chegel said:
Does not work, circular ref error, and at least 1 cell has a 0
--
chris


T. Valko said:
Try these:

A1: =IF(B1=0,"",B1)
B1: =IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


chegel said:
I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells with
a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
Click to expand...
Click to expand...
Click to expand...
 
I assumed you had already set iteration and you wanted to use an intentional
circular reference. If you set iteration it does exactly what you asked for.
You have to set iteration *before* you enter the formulas or you will get
the circular reference warnings.

--
Biff
Microsoft Excel MVP


chegel said:
Does not work, circular ref error, and at least 1 cell has a 0
--
chris


T. Valko said:
Try these:

A1: =IF(B1=0,"",B1)
B1: =IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


chegel said:
I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells
with
a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
Click to expand...
Click to expand...
Click to expand...
 
Unfortunatly I cannot use a macro.


--
chris


Gary''s Student said:
Try this small event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:B1")
Set ra = Range("A1")
Set rb = Range("B1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Intersect(Target, ra) Is Nothing Then
ra.Value = rb.Value
Else
rb.Value = ra.Value
End If
Application.EnableEvents = True
End Sub

If either A1 or B1 is changed, the other will mimic the change.
--
Gary''s Student - gsnu200758


chegel said:
Does not work, circular ref error, and at least 1 cell has a 0
--
chris


T. Valko said:
Try these:

A1: =IF(B1=0,"",B1)
B1: =IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells with
a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
Click to expand...
Click to expand...
Click to expand...
 
I did try this, didnt work at default of 100 and .001. But I think I need a
programed solution anyway. And I think I was able to do this very simply
years ago in 2000 or 1997 version.
--
chris


T. Valko said:
I assumed you had already set iteration and you wanted to use an intentional
circular reference. If you set iteration it does exactly what you asked for.
You have to set iteration *before* you enter the formulas or you will get
the circular reference warnings.

--
Biff
Microsoft Excel MVP


chegel said:
Does not work, circular ref error, and at least 1 cell has a 0
--
chris


T. Valko said:
Try these:

A1: =IF(B1=0,"",B1)
B1: =IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells
with
a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
Click to expand...
Click to expand...
Click to expand...
 
I turned iteration on, and I am rechecking, this seems to be working now.
--
chris


T. Valko said:
Try these:

A1: =IF(B1=0,"",B1)
B1: =IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


chegel said:
I am creating a form to allow input from either of 2 seperate cells.
Example:a1=b1, b1=a1 , when entering in either cell it is copied to the
other. I need to suppres the circular reference error in both cells with
a
blank ("").
I get 0 instead of blanks with the following:
[a1] =IF(ISERROR(B1),"",B1)
[b1] =IF(ISERROR(A1),"",A1)
Click to expand...
Click to expand...
 
Back
Top