• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

circular reference

H

HVK1986

New Member
Hi all,

I have data as under

A1=1

B1=2

C1=3

D1=4

E1=1+2+3+4=10.

Now if i change A1=2, in E1 i want value to be 12(10+2), if i change B1=3, i wamt E1=15(10+2+3)...


Pls guide
 
Good day HARRY


Just put this in to E1......=SUM(A1:D1)


Not sure why you put "circular reference" in your title
 
Hi Bob ,


What the OP wants is that every succeeding value of E1 will take the changed value in A1:D1 and add it to the earlier value of E1 ; thus if initially , the sum of A1:D1 is 10 , when the value in A1 is changed from 1 to 2 , the value in E1 will now be the sum of its earlier value of 10 and the present value in A1 ( which is now 2 ) i.e. 12.


Similarly , if the value in B1 is changed from 2 to 3 , then again the value in E1 will take the present value of B1 ( which is now 3 ) , and add it to the value which is already in E1 ( which is 12 ) to get a new value of 15 in E1.


Narayan
 
Hi Harry ,


The easiest way to do this is via a Worksheet_Change event macro ; any time a value in the range A1:D1 changes , the macro fires , and the value which is responsible for firing the macro , can get added to the current value in E1 to give a new value of E1.


Narayan
 
Good day NARAYANK991


Have re-read the post again and again and unless there is some thing glaringly obvious that I am not seeing the formula in my first post does what you say in your first post.
 
Hi bob!


I shall attempt to explain. Let say we start with this in A1:D1

[pre]
Code: 
1
2
3
4
Value in E1 would be 10. All good here. The problem arises in that OP wants to change one of the cells and add to this running total. So, if we change A1 to 5, like so:

5
2
3
4
[/pre]
The value in E1 needs to be 10 (from previous sum) + 5 that we just added, thus giving an answer of 15. The SUM function you original posted would only evaluate to 14.


Personally, I don't like methodologies that overwrite previous data while still using outputs from that same data. It doesn't provide good trace-ability. But, I do see this question asked occasionally, and as Narayan stated, the only way is via a macro.
 
Good day Luke M


I think these words "some thing glaringly obvious " sums up the problem....me! could not see the need for the code due to the old saying...cant see the wood for the trees :).

Time to take some insulin :(
 
Good day HARRY


You may find the link of use, but I have found that every has the same view as Luke M, this method is not recommended. One key input mistake and your data is screwed and you have no way of knowing as there is no data trail.


https://www.google.co.uk/search?q=how+do+you+create+a+running+total+in+excel&hl=en&sa=X&tbo=u&source=univ&tbm=vid&ei=FtzlUNW8Nsek0QXHwICIAg&ved=0CDMQqwQ&biw=1425&bih=685
 
Hi Bob / Luke / Harry ,


I don't see any problem in traceability if you are willing to have an audit trail sheet , which will be hidden ; every time any cell in the data range ( A1:D1 in your case ) is changed , the audit trail sheet can be updated with the cell reference that was changed , the earlier value and the changed value. Of course , as with the updating of the output value ( E1 in your case ) , this will also have to be done in the same macro that updates the output.


Narayan
 
Hi Harry,


Can you please try the below for me..


Right Click on sheet name, and go to View Code, paste the below Code..

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A1:D1]) Is Nothing Then Exit Sub
If IsNumeric(Target) Then
[E1] = [E1] + Target
End If
End Sub
[/pre]

Above coding is very short and not fully validation proof..

I am sure Narayan will came up with some bulletproof coding.. :)


Regards,

Deb
 
Hi Harry ,


Can you download this file and see if it is what you were looking for ?


http://speedy.sh/aVx7R/Example-Harry.xlsm


There is a problem with the cursor movement , which I have not yet solved ; I hope that is not a problem.


For those who cannot download , and would like to comment on the code :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("Data_Range")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim New_Value As Double, New_Sum As Double
Dim Old_Sum As Variant, Old_Value As Variant
Dim Changed_Address As String

Dim dr As Range, oc As Range
Dim aud As Worksheet, ds As Worksheet

'  This is a hidden worksheet which will preserve a history of changes in the data
Set aud = ThisWorkbook.Worksheets("Audit Trail")

'  This is the sheet which has the data range , change as required
Set ds = ThisWorkbook.Worksheets("Sheet1")
ds.Activate

Set dr = ActiveSheet.Range("Data_Range")           '  Data_Range is a named range
Set oc = ActiveSheet.Range("Output_Cell")          '  Output_Cell is a named range

Old_Sum = oc.Value
New_Value = Val(Target)
Application.Undo
Old_Value = ActiveSheet.Range(Target.Address).Value
If IsEmpty(Old_Sum) Then Old_Sum = Val(Application.WorksheetFunction.Sum(dr))
Target = New_Value
New_Sum = Old_Sum + New_Value

Changed_Address = Target.Address

aud.Activate
Call Copy_to_Audit_Trail(Changed_Address, Old_Value, New_Value)
ds.Activate

oc.Value = New_Sum

Set dr = Nothing
Set oc = Nothing
Set aud = Nothing
Set ds = Nothing

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Public Sub Copy_to_Audit_Trail(celladd As String, oldval As Variant, newval As Double)
Dim last As Long

With ActiveSheet
last = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1")
.Offset(last, 0) = celladd
.Offset(last, 1) = oldval
.Offset(last, 2) = newval
End With
End With
End Sub
[/pre]
Narayan
 
Very nice Narayan. I like the idea of an Audit Trail. If I had free reign, I'd do things different (for project, not macro). I'd keep a running tally of all numbers (thus, traceability) and use some formula to show last 5 numbers and total on a separate sheet. But, the great thing about XL is there are lots of ways to do the same thing. =)
 
You must log in or register to reply here.
Back
Top