Sum formula

Posted by Javaid on April 01, 2001 4:31 AM

I have two cells A1 and B1. My requirement is whenever i Update a value in A1, the cell B1 should be updated automatically with the value of a1+b1. A reference error occurs if we use b1=a1+b1. I need to resolve this using Excel 2000.

Posted by Jacob Hilderbrand on April 01, 2001 11:19 AM

You cant do it by b1=a1+b1 since that would create a circular referance however, it can be done through VB. Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Dim My_Value
If Target.Address = "$A$1" Then
Range("A1").Select
My_Value = ActiveCell.Value
Range("B1").Select
ActiveCell.FormulaR1C1 = ActiveCell.Value + My_Value
End If
End Sub

Hope this helps.

Jacob