• 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.

How can I keep a Prior Total Value and Allow for a New Total Value to update?

S

smc001

Member
I have a master report and it pulls from various sources. When it updates it will of course change the total value.


I need my report to automatically capture what the prior value was and place it into a prior total value cell right next to the updated new value. (I have this set-up on a chart)


This enables us to see the dollar amount increase in spending from week to week. As well I would like to put a percentage cell referencing the increase amount from prior total value to updated current total value.


Any suggestions...
 
Hi, smc001!

I think you'll have to involve VBA to do that. Something like a button to manually trigger the update from data sources, and place stuff in this order:

1) Selection.Offset(0, 1).Value = Selection.Value (with the cell to be updated selected)

2) update from data sources

3) report stuff

If it's only storing one previous value it wouldn't be hard, but if you want a N-period history, from VBA point of view are just a few lines more, but I don't know how will that impact in your report.

Regards!
 
Sounds good... I was going to ask whom ever responded to provide details. KISS... :) I couldn't find my way out of a paper bag at this point let alone create a vba... ;) got anything simpler? Thanks
 
Hi, smc001!

I'll need the structure of the book, if it's with data (not necessarily real, maybe random), so you don't have to change anything and can wrap around without messing with VBA.

Otherwise I will make an schema that you'll have to modify and adequate. So if you can upload a file, it'll be easier.

Regards!
 
Let me explain the setup. I have 2 worksheets in a work book. (worksheet 1=wks1) master report, (worksheet 2=wks2) graphic/chart/pie display.


The (wks2) data is updated from the wks1 cell M21. M21 is stable it just represents the total dollar amount and as the report data is updated with new sale amounts from various sources the total dollar amount changes.


The wks2 cell E32, which is linked to wks1 M21 changes as dollar amount change


I wanted to add on wks2 a cell next or by cell E32. Maybe cell E31, that would capture the value from E32 before it's updated.


I just need the total value from of the prior report captured.


So if my report yesterday said Total $16,000.00 in cell E32 and I update E32 with new sales of $20,500 today I need the $16,000.00 to transfer to E31. Then in E32 a new total will populate. Does that make sense ;)
 
Hi, smc001!

I uploaded this:

http://www.2shared.com/file/HtdfmL5s/How_can_I_keep_a_Prior_Total_V.html

Look if it works for you.

The idea is;

a) updating manually as today but before pressing the command button so as to save the previous value wanted

b) updating automatically inserting all the code into the command button event (as I don't know how the update is performed I used a fixed update of 10K U$S each time you presses it).

If need help with the VBA code, just ask.

Regards!
 
Hi ,


The following code does it , I think ; replace the Msgbox statement with the required cell assignment statement. Also , add the required Application.Intersect statement to execute this macro , only in the event of your cell being modified.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim OldValue As Variant

With Application

.EnableEvents = False

.Undo

OldValue = Target.Cells(1).Value

.Undo

.EnableEvents = True

End With

MsgBox "Old Value was " & OldValue

MsgBox "New Value is " & Target.Value

End Sub


This is from the following link :


http://www.eggheadcafe.com/microsoft/Excel-Programming/30229585/how-capture-cell-value-before-change-event-fires.aspx


Narayan
 
Hi, smc001!

Here you've got another approach to an analogue problem;

http://chandoo.org/forums/topic/track-the-changes

Hope it helps too.

Regards!
 
How so since I am super green, like fish out of water... In respect to the wks1 cell M21(Total Value) and wks2 cell E32 (Total Value linked) to wks2 cell E31 (where I need the prior total value to populate)... How do I input that into the VBA coding and into my speadsheet?


As well there are other cells from WKS1 that update when I do the typcial report updates... Will this VBA have an impact on the other cells?


I need this only to apply to wks1 cell M21 and wks2 E32 to populate prior total in wks2 cell E31.


Please advise... Thank You!
 
FYI!!! All your options are great! I really appreciate all the hard work that has gone into helping to find a solution!!! I just need to know how to apply, where to apply and what changes I need to plug in so I can apply this to as a solution. Humor me please I function in Super Tiny Baby Steps mode... Gratis!
 
Hi, smc001!

If you can upload the file where you want to keep the previous value, I'll reupload it for you with the command button and VBA code ready to use.

Regards!
 
Okay! I will need good directions on how to do this myself...I have other reports I need to do the samething with... Quit a few other eports infact... :)


Here's the link... to the report... What you see in red on the worksheets are linked areas of report...


Here's the link...


https://skydrive.live.com/redir.aspx?cid=8c4598bed3ef132a&resid=8C4598BED3EF132A!116&parid=8C4598BED3EF132A!108&authkey=!AAxujAWZtgSTP-w


I hope you can open it... large file... I have norton and various anti virus app's always running and updated on my system... If you feel okay with downloading?


I had to WinZip the file...


Thank You!
 
Scratch the last link this one should work:


https://skydrive.live.com/redir.aspx?cid=8c4598bed3ef132a&resid=8C4598BED3EF132A!117&parid=8C4598BED3EF132A!108&authkey=!ADGhnH76-yJ_0_s


thanks
 
Hi, smc001!

https://skydrive.live.com/?cid=8c4598bed3ef132a&id=8C4598BED3EF132A!117#cid=3A8BDC8CDF4D772C&id=3A8BDC8CDF4D772C!172

I added a big light-blue button to sheet 1 (that's not printable), and after confirmation it stores the value of cell E31 in sheet 2 into cell E32.

I added the value 500 in E11 in sheet 1 (Communications - Actual) for testing and it was reflected in chart and sheet 2. I think it does the job.

Let me know if it works for you.

Regards!

PS: Added an extra worksheet fyi, after writing down you can delete it.
 
WONDERFUL!!!!! It works like Magic!


Now how do I add this (button-magic) to my other reports... I see you can go to developer tab, view code...Under the VBAProject report Test Total, sheet1 I see code is placed...Can I copy this code and do the same thing for the other reports? Is that the only place the code is inserted?


Oh, as well I need to change the color of the blue button to (celery or grass) green...and is it possible to have the button only on the chart page?


If not that's fine...As you could probalby guess I not very proficient with VBA ;)


Please advise...
 
Hi, smc001!

You have to do two things:

First, copy the button (on the Excel menu, tab Programmer/Programing, Design Mode, then select the button, right click, copy, go to new workbook, menu, tab Progr, Design Mode, paste, right click, Properties, change name to "cmdUpdate").

Second, copy the VBA code (ALT-F11, in left panel select 'VBA Project (Report test...', double click, then select source 'Sheet1(Report ....', copy everything in right panel, now in left panel select 'VBA Project XXXXX...', where XXXXX... is the new workbook, select target sheet, paste in the right panel).

And that's all.

If you want to move the button to chart's sheet, the procedure is the same, but instead of copying/pasting is cutting/pasting within the same workbook.

Ah, in the same window where you change the name to the command button you have the font option to change the colour.

Advise if something doesn't go as expected.

Regards!
 
Thank You! Thank You! for the Details!!! NOW I AM OFFICIALLY DANGEROUS!!! Kid in a Candy Store and Licenses to Drive!


I will let you know if I can make this work. Keep your Fingers Crossed!
 
Rats! I got the button to look like it works... It does not pop up anything like your example...as well it's kind of random when I think it's work, but it really isnt' working well.....


These are the reference info... maybe you can make it work this way...


I put the button on the Base report under Row 2-3 section M


Base report tab:


M21 is what populates the total value: Formula: sum=(m9:m19)


Chart tab:


E32 Current Total: $ Formula: ='Base General Rollup Report'!M21


E34 Previous Total: $ Formula: ='Base General Rollup Report'!M21


E36 % Diff. Previous/Current: 0% Formula: =((E32-E34)/E34)*100


Heres' the code:


Option Explicit


Private Sub cmdUpdate_Click()

' constants

' declarations

Dim I As Integer, A As String

' start

' save previous selected cell

A = ActiveCell.AddressLocal

' ask for confirmation

I = MsgBox("Are you going to start the update process?", _

vbApplicationModal + vbInformation + vbOKCancel + vbDefaultButton2, _

"Update start message")

' exit if aborting

If I = vbCancel Then

MsgBox "Update process cancelled", _

vbApplicationModal + vbExclamation + vbOKOnly, _

"Update cancelled"

GoTo Update_Exit

End If

' process

' here you may include all the stuff (I don't know what) to perform the data update

' 1) first of all, saving the previous M32 value in wks2

Worksheets("Base General Rollup Chart").Cells(32, 5).Value = Worksheets("Base General Rollup Chart").Cells(34, 5).Value

' 2) update value M21 in "Base General Rollup Report (just add 10K for the example)

Worksheets("Base General Roll-up Report").Cells(32, 5).Value = Worksheets("Base General Rollup Report").Cells,(32, 5).Value

' 3) other updates

' end

' completion message

Beep

MsgBox "Update process finished", _

vbApplicationModal + vbExclamation + vbOKOnly, _

"Update end message"

Update_Exit:

' reposition the previous cell

Range(A).Select

End Sub


Can you work your magic?...I will catch zzzz 3 hrs and be back...Thank you!!!!
 
Hi, smc001!

I thought it was working, placed in the first sheet (not in the chart one) it worked fine in the file I sent you back.

If you moved the button from one sheet to anotther (the placement is irrelevant, it's just visual) you have to be sure of two things:

1) that after moving or copying the name of the button remains "cmdUpdate" (you can see the Properties windows selecting the tab "Program/Programming", then clicking on the Design Mode button, and then right click on the control itself)

2) that you have moved the VBA code associated to it to the right sheet (the VBA you posted before this)

With those two considerations, if the report is the same you uploaded and I sent you back, it should work.

Otherwise, just please reupload or email me the new file and I'll check it.

Regards!
 
I have made the CMDUPDATE changes and everything per your instruction. One thought about the cell M21 wks1 and E32 and E34.


Are each of those cell to be blank? Of course thers'a sum formula in M21 because it's summing a total column..


As for the E32 its a link from M21...As for E34 that is linked from E32.


Could this be the problem and what would your suggestions be to remedy. Thanks!!!
 
Hi, smc001!

Cell M21 in wks1: it shouldn't be blank, it has a formula =sum(m10:m20)

Cell E32 in wks2: it might be blank in the original file, previous to the first use of the not so magical button ;-)

Cell E34 in wks2: it wasn't involved in the process... are you referring to E31 instead?... if so, it shouldn't be blank, it has a formula =wks1!m21.

Wanna mail me the file if you can't solve this issue?

Regards!
 
You must log in or register to reply here.
Back
Top