There a simple way to do this?

  • Thread starter Thread starter mmednick
  • Start date Start date
M

mmednick

I start with a collumn of financial data. The value of the last row is
the sum of all the other rows. If this number is negative however, I
want the value of the number in the first row to update by adding the
absolute value of the last row figure to itself.

A 100 <-- I want this number to recalculate with the absolute value of
E
B 50
C 50
D-250
E -50 <-- sum of rows A through D

I know this is troublesome but I think I saw somewhere a colleague used
a function to copy out values to other cells in order to break the
circular refernce in a problem like this.

Anyone have any suggestions?
the n00bish
 
mmednick,

You could use the sheet calculate event. Copy the code below, right - click
the sheet tab, select "View Code" and paste in the window that appears. Code
is written for column E, rows 1 to 5. You could generalize the code to fix
any number of columns: post back if that is your desire.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("E5").Value < 0 Then
Application.EnableEvents = False
Range("E1").Value = Range("E1").Value - Range("E5").Value
Application.EnableEvents = True
End If
End Sub
 
What you are doing is: if E1 is negative, then adjust A1 to bring it to zero:


Sub Macro1()
If Range("E1").Value < 0 Then
Range("A1").Value = Range("A1").Value - Range("E1").Value
End If
End Sub

will perform this operation
 
Back
Top