Accumulating total if Target cell contains formula

**DeeBee** · 09-03-2015, 03:15 PM

Good day,

I need some help please? I need to accumulate the value of a cell (I39)from the value of another cell (G31) every time it changes and the code below does the trick,but only if the target cell is a manual input. How do I change it to work same for when the target cell (G31) contains a formula (=SUMG1;G30).

Please help me?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = ["$G$31"] Then
[I39] = [I39] + [G31]
End If
End Sub

**JasperD** · 09-03-2015, 03:22 PM

Instead of worksheet change, use:

Please Login or Register  to view this content.

**skywriter** · 09-03-2015, 03:27 PM

If G1:G30 as entered manually you could try this:

Please Login or Register  to view this content.

**TMS** · 09-03-2015, 04:11 PM

Maybe:

Please Login or Register  to view this content.

Regards, TMS

Edit: Added

Please Login or Register  to view this content.

**TMS** · 09-03-2015, 05:59 PM

@Skywriter: thanks for the rep.

Note that .precedents only works if the cells precedents are on the same worksheet. If the precedents were on another worksheet, you'd need a Change event handler on that sheet which then checked the outcome on the sheet with the dependent cell (hope that makes sense).

**DeeBee** · 09-04-2015, 12:39 AM

Hi JasperD,

Thank you for your assistance. Your code works well, but if the next or updated sheet contains the same values, ie, same sales for two days, then I39 won't add the new (although same) value. Hope I make sense, and hope you can assist?

Blessings

**DeeBee** · 09-04-2015, 12:46 AM

Hi TMS,

Thanks for the code, it works great, but it keeps adding the first value also. IE, if first value was 20 and the next to add was 10 then I'd get accumulated value of 50 instead of 30? Can you fix that? Thanks for your help!

**DeeBee** · 09-04-2015, 12:49 AM

Hi Skywriter,

Thanks for your help. The values in G1;G30 are a result of other formulas, but thank you for your help!

Blessings

**TMS** · 09-04-2015, 05:41 AM

The code was intended to demonstrate a couple of options. You need to comment out the one you don't want.

Maybe this will clarify:

Please Login or Register  to view this content.

If this doesn't resolve the issue, you probably need to give a few examples of what you expect to see. For example, assuming I39 starts at 0 (or blank) and G31 contains the formula: =SUM(G1:G30) then: G1=1, G31=1, I39=1; G2=2, G31=3, I39=4; G3=3, G31=6, I39=10; ... etc

See the example workbook.

Regards, TMS