Displaying the amount of the latest change to a cell

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Yes - that is possible
Will post some sample code when back at my PC
 
Upvote 0
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
ABCDEFGHIJ
1Available from total start of $1000last entry
2$579.65$95.00total expense1234567
3-$420.35$15.00$16.00$19.00$55.00$80.00$140.35$95.00
Sheet2
 
Upvote 0
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
A
B
C
D
E
1
ProjectPrevCurrentChange
2
Proj1
25​
25​
0​
3
Proj2
22​
22​
0​
4
Proj3
33​
33​
0​
5
Proj4
44​
44​
0​
6
Sheet: Compare

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top