Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How can I do the following in excel? "IF A1 is not = B1, then A1 is replaced by B1 , else contents in A1 remain in A1" Thx!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    If I understand your description correctly, A1 should always contain the same value as B1, for if it's not equal to B1, it should be replaced by B1.

    You can use the formula

    =IF(B1="","",B1)

    in cell A1 for that.

  3. #3
    New Lounger
    Join Date
    Jul 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783400' date='07-Jul-2009 14:44']Welcome to the Lounge!

    If I understand your description correctly, A1 should always contain the same value as B1, for if it's not equal to B1, it should be replaced by B1.

    You can use the formula

    =IF(B1="","",B1)

    in cell A1 for that.[/quote]

    Thx for the reply. I have a value in A1, let us say 20. I want this value to change to 5 if B1 is 4, otherwise I want it to remain 20. Hope I am explaining it well.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't do that with a formula, because that would lead to a circular reference: a formula in a cell should not refer to the cell itself (except under very special circumstances).

    You can use the Worksheet_Change event for this:
    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Enter or copy/paste the following code into the module that appears:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Range("A1:B1"), Target) Is Nothing Then
    	If Range("B1") = 4 Then
    	  Application.EnableEvents = False
    	  Range("A1") = 5
    	  Application.EnableEvents = True
    	End If
      End If
    End Sub
    Warning: users will have to enable macros, otherwise the code won't run.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •