I know there are some excellent spreadsheet gurus here. Here's my need....I have a spreadsheet that imports values from another (non-excel) program. So the value in the cells changes with each update. I need to keep a running total of the last cell (in this case, cell A65) How do I get the spreadsheet to re-add the same cell on each update, thus keeping a running total?? Example: A65 has a value of 10. When the other program updates, A65 is now 11. The running total cell (let's say A66) is the cell I need a formula to tell me the running total is now 21. After the next update, A65 is now 12, so A66 should be reading 33. Any ideas?
=sum('select the cells you want it to keep a running total for') should do the trick, if i'm reading your post correctly.
Are you writing the macro that populates the data? It would seem that you would have to do some sort of hidden column or row to capture the running totals prior to populating the data so that you could then add the new populated data to the running total. Does that make sense?
That makes sense, but that's not it. The data is populated from another program that tracks the process variables. So while the variables change continuously, the data is imported into excel once a minute (or once and hour, my choice). So let me clear up the example: Program RV sees a process variable of 300, and imports 300 into cell A65. One minute later, Program RV a sees a process variable of 320, and imports 320 into A65. My running total for the past two minutes is 620. But I would have to add A65 twice to get that value. Now then, I could set it up to place the new value into a new cell each time, thus allowing for a sum total of entire column. But if I use minute updates, that would become a huge spreadsheet within a few hours.
Not really, you don't have to keep capturing every piece of data every minute. Minute One Data (A) comes in Total (T) = A Copy T to Variable (B) Minute Two Data A comes in again. T = A + B Copy T to B Minute Three Data A comes in again T= A + B Copy T to B again. You should only have one extra variable which is the copy of your previous total. Correct?
I concur with Mr. Baiter. You want a macro to add the new cell to the previous total. In any case, the way you want to keep the total, without keeping the historical data, you can't use a formula. You'll have to use a macro that grabs the data you want and destroys the rest. Are you using a macro to pull in the data? Or is this other system pushing the data to excel or what?
You need to use a macro for this. I'll refer to the two files as the Source File (where the value changes) and the Historical File (where you want to keep a historical record). In the Historical File, create a worksheet called Lookup. In cell B1 make a reference to the cell you're interested in from the Source File. You can explicitly reference it (ex: A1), or if that cell location is going to change, name the cell (ex: "ExportResult", etc.). In cell A1, give a label for the data (this is for your own reference). In the Historical File, create a worksheet called Master. In cell A1, enter 1 and in cell B1, enter the initial value from the Source File. In cell C1, enter the current date. In the Historical File, go to the Lookup worksheet and in cell A2, enter "Row Count". In cell B2 enter the following formula: "=MAX(Master!A1:A50000)" Now you're ready for the macro part of this. Code: Sub GetHistoricalData() Dim li_RowCount As Integer Dim ldt_Today As DateTime Dim ls_Range As String li_RowCount = Sheets("Lookup").Range("B2").Value ls_Range = "A" + CStr(li_RowCount + 1) Sheets("Master").Range(ls_Range).Value = li_RowCount ls_Range = "B" + CStr(li_RowCount + 1) Sheets("Master").Range(ls_Range).Value = Sheets("Lookup").Range("A2").Value ls_Range = "C" + CStr(li_RowCount + 1) Sheets("Master").Range(ls_Range).Value = Today() End Sub To run this, you would just need to open the Historical File, refresh so it pulls the latest value from the Historical File and then run the macro. It'll put the value in column B and the current date in column C. If you want the macro to automatically run each time you open the Historical File, name the macro Autpen. Hopefully that's pretty straight forward. Let me know if you have any questions.
Thanks guys, I'm tied up with something else right now, but I will try your suggestions later. I think I understand now, but don't have time to test it
Private Sub Worksheet_Change(ByVal Target As Range) ' Ensure that it was cell "A1" that changed If (Not Application.Intersect(Target, Range("B1")) Is Nothing) Then ' Call your routine ' Update C1 with the contents of A1 Range("b2").Value = Range("b2").Value + Range("b1").Value End If End Sub others seem to be bigger excel buffs, so it is probably safer to go w/ their answers.
Thanks again. I figured out an alternative method using my dcs board instead of excel, so this little idea died a quick death. Much like most of my ideas around here. /thread closed.
