|
replace error with blank
Try these:
A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
replace error with blank
Does not work, circular ref error, and at least 1 cell has a 0
-- chris "T. Valko" wrote: Try these: A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
replace error with blank
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" wrote: Does not work, circular ref error, and at least 1 cell has a 0 -- chris "T. Valko" wrote: Try these: A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
replace error with blank
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" wrote in message ... Does not work, circular ref error, and at least 1 cell has a 0 -- chris "T. Valko" wrote: Try these: A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
replace error with blank
Unfortunatly I cannot use a macro. -- chris "Gary''s Student" wrote: 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" wrote: Does not work, circular ref error, and at least 1 cell has a 0 -- chris "T. Valko" wrote: Try these: A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
replace error with blank
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" wrote: 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" wrote in message ... Does not work, circular ref error, and at least 1 cell has a 0 -- chris "T. Valko" wrote: Try these: A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
replace error with blank
I turned iteration on, and I am rechecking, this seems to be working now.
-- chris "T. Valko" wrote: Try these: A1: =IF(B1=0,"",B1) B1: =IF(A1=0,"",A1) -- Biff Microsoft Excel MVP "chegel" wrote in message ... 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) -- Chris |
| All times are GMT +1. The time now is 11:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com