replace error with blank

[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)

 

[T. Valko]

Try these:

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

 

[chegel]

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

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)

 

[Gary''s Student]

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

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

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)

 

[T. Valko]

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

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

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)

 

[chegel]

Unfortunatly I cannot use a macro.


--
chris

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

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

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)

 

[chegel]

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

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

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

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)

 

[chegel]

I turned iteration on, and I am rechecking, this seems to be working now.
--
chris

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)