Displaying the amount of the latest change to a cell

[sduttonusa]

I have dozens of worksheets under the general category of "Projects". There is a place in my workbook where I have a running total of the balance for all the projects. Is there a way to monitor the total Projects cell and display the amount (plus or minus) of the last amount that was recorded somewhere throughout the worksheets in Projects?

 

[Yongle]

If EVERY change is TimeStamped then balances at a point in time can be extracted and changes calculated

Otherwise perhaps click on a button to get use VBA to record the current value in "total projects cell" periodically

Q1 Do you want to keep an ongoing record (eg day by day) or do you simply want to know the amount of change since you last pressed the button?
Q2 Are you wanting to monitor only ONE value or several values?

 

[sduttonusa]

Thanks for your quick reply. Ultimately, I'm wanting to monitor about 7-8 values. A little more explanation . . . on every spreadsheet in my workbook, I have a "table of totals" in the upper right corner that will update the workbook's totals as soon as anything is entered on any spreadsheet. So I go to my spreadsheet "Building Renovations" which is one of a dozen spreadsheets in the Projects section of my Excel workbook. If I enter a $500 February expense in the Plumbing column on the Building Renovation spreadsheet, I will see in my table of totals that the YTD Plumbing category went down $500. Now if look at the table of totals just before entering the $500 and see that the current total is $1000, when I enter the $500 I will then see the new total being $500 . . . easy. But when dealing with ten's of thousands of dollars down to the penny per transaction, seeing the total change properly is not so easy. So I was hoping that there was a way (I noticed Excel has a "Watch" feature) to have the amount of the change from the current total to the new total (once the number is entered) displayed. And the reason for needing the display is that when a "deposit" is made on some of the projects, the money is disbursed (by a percentage calculation) throughout several sub-categories. So if when the new totals are displayed in my "table of totals" at a glace I could see that the "deposit" was properly disbursed throughout the sub-categories.

 

[Yongle]

Excel does not allow you freeze formulas at a point in time (unless auto-calculation is switched off)
I think you are referring to the watch window feature but I do not think that is helpful here - just like everything else it lives in the "here and now"

How about
- flagging "cells containing values to be redistributed" in some way (flagA) and also flagging the "recipient cells" (flagB) and have a "proof" formula somewhere prominent that should always sum to zero

 

[sduttonusa]

Hi,
Sorry for the delay in my response. Could you provide a simple example of what you mean by flagging? If cell S10 has my calculated balance as 10,000, and I then add 1000 to A7 which will immediately make S10's calculated value to change to $11,000 . . . what would a formula using flagging look like?

 

[sduttonusa]

Just a thought . . . would there be a way to create a one click function which would copy all the current calculated values from one set of cells into a new set of cells (which would then make those numbers no longer calculated). After that, then I could "compare" the new values that I enter against the table with the static numbers?

 

[Yongle]

Yes - that is possible
Will post some sample code when back at my PC

 

[pedro-egoli]

This may not be what you are after but will reactivate thread
It gives a running balance and the last entry in a row

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Available from total start of $1000	last entry
2	$579.65	$95.00	total expense	1	2	3	4	5	6	7
3			-$420.35	$15.00	$16.00	$19.00	$55.00	$80.00	$140.35	$95.00
Sheet2

 

[Yongle]

Here is something very simple for you to start with which monitors the value change in the same cell on each sheet. This can be expanded etc as we go along
- you may want different cells in each sheet etc , or more than one cell in each sheet etc
- let me know what else you need

To Test

In a COPY of your workbook add a new worksheet with name "Compare"
Go to to VBA with {ALT}{F11}
Insert a new Module with {ALT} I M
Paste the code there
Amend the value of cel to a reference that has a value in every sheet (do not worry for the moment if any sheets need excluding - that comes later)
Return to Excel with {ALT}{F11}
{ALT}{F8} to list macros
Run FreezeValues and sheet "Compare" ressembles picture below
Columns C & D contain formula
Column C formula =Proj1!A20
Column D formula =B2-C2
And now you can monitor changes in A20 in each sheet

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Project[/td][td]Prev[/td][td]Current[/td][td]Change[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Proj1[/td][td]

25​

[/td][td]

25​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Proj2[/td][td]

22​

[/td][td]

22​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Proj3[/td][td]

33​

[/td][td]

33​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Proj4[/td][td]

44​

[/td][td]

44​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Compare[/td][/tr][/table]

Sub FreezeValues()
    Const cel = "[COLOR=#ff0000]A20[/COLOR]"
    Dim Ws As Worksheet, Sh As Worksheet, r As Long
    Set Sh = Sheets("Compare")
    
'clear previous values
    Sh.Cells.Clear
'new values
    r = 1
    Sh.Cells(r, 1).Resize(, 4) = Array("Project", "Prev", "Current", "Change")
    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name <> Sh.Name Then
            r = r + 1
            With Sh
                .Cells(r, 1) = Ws.Name
                .Cells(r, 2) = Ws.Range(cel)
                .Cells(r, 3).Formula = "='" & Ws.Name & "'!" & cel
                .Cells(r, 4).Formula = "=" & Cells(r, 2).Address(0, 0) & "-" & Cells(r, 3).Address(0, 0)
            End With
        End If
    Next Ws
End Sub

 

[sduttonusa]

Thanks for all the help. I created a macro (with a button) that copies the calculated cells and pastes their values a couple of columns away. I'll run that macro just prior to entering my data for that worksheet And in between the two columns, I have a column that displays the difference between the calculated cells and the cells that were pasted.