Running Totals

[freddie mitchell]

I would like to create a running total for a single cell which then adds up in another!
Example I add "4" to cell b5 this then appears in cell b6. I then add "5" to cell b5 then "9" apperas in b6

How would I get this to work.
Thankyou so much for any assitance freddie

 

[freddie mitchell]

We have about 500 forms come in from the around great Britain and theres a comment section. My job is to create a spreadsheet that can collect all this data. Now the comments vary in size so have no regualr pattern that means they have to be typed out. This will take up a lot of space on the spread sheet, so if I could remove sed text after its been typed and put it somewhere else.. all sorted!

 

[freddie mitchell]

Also is there anyway of locking a cell but still allowing macros to operate in it? thanks

 

[GQue]

You may want to consider setting up a database for these 'form entries'. That would greatly help reduce all the excel work you need to do. Just would need to create a front end that populates the db (if you have SQL running in your company).

Locking a cell? I know that you can lock down cells in the sheet but then you need to protect the sheet, and only cells NOT locked down are able to be changed. That help? If so, Right Click on the cell, under FORMAT, you can LOCK CELL and then you just need to PROTECT SHEET when you are done.

 

[malverd]

Hi,

I tried to enter this into Excel 2003. I made a new spreadsheet. The pressed alt F11 to bring up the Visual Basic editor. I highlighted This Workbook and then entered the code into the code window. I clicked Saved and then chose save as Book1. I then tried to get the macro to work but it won't.

I'm sure I'm missing a step, but I can't figure out what it is.

Thanks for the help

 

[GQue]

By default, Excel's security level is set to High so it won't prompt you to enable macros and will automatically disable them.

If I remember correctly, it is under Data | Macro | Security... Change the level to Medium so you will be prompted to Enable Macros.

That should fix you up...

 

[malverd]

Thanks that helped. I am using DDE and one cell is updating automatically, but that value is not being added to another cell. Shouldn't this work with DDE?



I changed it to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Row = 4 And Target.Cells.Column = 11 Then
Cells(4, 13) = Cells(4, 11) + Cells(4, 13)
End If
End Sub


The value in J4 is =IF(D4>=F4,J4,0)

Thanks

By default, Excel's security level is set to High so it won't prompt you to enable macros and will automatically disable them.

If I remember correctly, it is under Data | Macro | Security... Change the level to Medium so you will be prompted to Enable Macros.

That should fix you up...

 

[GQue]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Row = 4 And Target.Cells.Column = 11 Then
Cells(4, 13) = Cells(4, 11) + Cells(4, 13)
End If
End Sub

That code is stating that when Cell K4 is changed, to update M4 with the total of M4 + K4. That should be working just fine with that code.

As for the other formula... I believe you were assuming that 4,11 was J4, not K4.

Let me know if it was that simple...

 

[malverd]

Yes,

I meant
The value in K4 is =IF(D4>=F4,J4,0)

However, the value in K4 always updates, but cell M4 is empty and never updates.

Any idea why that would be?

I also just tried to use a circular reference in the spreadsheet. Can you look at my other post in the forum?

http://www.mrexcel.com/forum/showthread.php?p=2702249#post2702249

Thanks, appreciate the help

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Row = 4 And Target.Cells.Column = 11 Then
Cells(4, 13) = Cells(4, 11) + Cells(4, 13)
End If
End Sub

That code is stating that when Cell K4 is changed, to update M4 with the total of M4 + K4. That should be working just fine with that code.

As for the other formula... I believe you were assuming that 4,11 was J4, not K4.

Let me know if it was that simple...

 

[GQue]

Oh... since it is a formula update, the macro WILL NOT RUN. The macro will only run on a ACTIVE change (YOU DO IT) to the cell. Formulas are passive changes and that is the root of the issue...

I'm taking a look at the other post now...