Running Totals

Hi,
Bit of a junior at the excel business but hope I can ask this question clearly enough to be understood....

I'm trying to put together a data entry spreadsheet and I'd like to be able to:

Have cell B1 act as an numerical 'input' cell that I could enter a value into it time and time again while the input values then are being sequentially added up in a running total in the cells below the B1 input


I also then need to then to duplicate the above functions to do the same for the adjacent columns C thru to AZ with separate input cells in row 1


I'm glad I've found this forum and hope someone can help me with what I'm trying to do

Thank you!

You can do this with a little bit of VB code...

Private Sub Worksheet_Change(ByVal Target As Range)

Const sINPUT_RANGE = "A1:C1"
Const lTOTAL_ROW_OFFSET = 1

Dim rngCellLoop As Range
Dim vTmpValue As Variant

Application.EnableEvents = False

If Not Intersect(Range(sINPUT_RANGE), Target) Is Nothing Then
  For Each rngCellLoop In Intersect(Range(sINPUT_RANGE), Target).Cells
    vTmpValue = Val(rngCellLoop)
    If Not IsError(vTmpValue) Then
      With rngCellLoop.Offset(lTOTAL_ROW_OFFSET)
        .Value = .Value + vTmpValue
      End With
      rngCellLoop.Value = ""
    End If
    rngCellLoop.Select
  Next rngCellLoop
End If

Application.EnableEvents = True

End Sub

To insert this:

1. It's easiest if you only have open the workbook you want to apply this code to.
2. Press Alt-F11 to open the Visual Basic editor
3. Towards the top left of the screen will be a list of sheets in your workbook. Double click on the sheet you want to do your input in.
4. In the drop-down list at the top left of the main window select "Worksheet"
5. In the drop-down list at the top right of the main window select "Change"
6. The 1st and last lines of my code will appear automatically - paste the rest of the code in between them.
7. Close down the VB window

You'll have to save your workbook in either .xlsm format or .xls format - Excel will no longer allow you to save it as a .xlsx because it contains macros.

By changing the line that sets the range of cells you're using for input you can meet your requirements. For example, to use cells B1:AZ1 as the input cells change the 2nd line of code to:

Const sINPUT_RANGE = "B1:AZ1"

Hope this helps.