Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    summ column ... (2000)

    How to summ value from column in sheet instead formula?
    Example:
    summing from B5 to B69 put total in B3...
    summing from C5 to C69 put total in C3...
    ...
    summing from N5 to N69 put total in N3...

    the limit of each summ is the last filled cell in A and the last fille row in line 4

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summ column ... (2000)

    Why don't you want to use formulas?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summ column ... (2000)

    if is possible with macro because the table not is fixed but dinamic...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: summ column ... (2000)

    If you don't want a formula you can:

    Use the sum formula then copy/paste the value into the cells in row 3.
    OR
    perhaps you could manually add them together and then type in the value in the cells.

    I don't understand what is wrong with having a formula. Formulas are nice since they will update themselves when the data changes. if you do not have a formula, someone will have to manually edit the values in row 3 when the items change

    To update automatically without a formula you could Use a macro to do it and enter the value on a change in the sheet.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: summ column ... (2000)

    A formula can also be dynamic....

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: summ column ... (2000)

    If you put the array formula in A1 (confirm with ctrl-shift-enter):
    =MAX(IF(A5:A65536<>"",ROW(A5:A65536)))

    Then In B3:
    =SUM(OFFSET(B5,0,0,$A$1-4,1))

    And copied B3 to C3:N3, the sum range would expand to include any new items added into column A....

    Is this what you want: a formula that expands?
    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summ column ... (2000)

    Steve has given you several good suggestions, but here is a macro:

    Sub MakeSum()
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim lngCol As Long
    lngMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
    lngMaxCol = Cells(4, Columns.Count).End(xlToLeft).Column
    For lngCol = 2 To lngMaxCol
    Cells(3, lngCol) = Application.WorksheetFunction.Sum( _
    Range(Cells(5, lngCol), Cells(lngMaxRow, lngCol)))
    Next lngCol
    End Sub

    It will have to be run each time the data change.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summ column ... (2000)

    special tks to Hans, great tks to Steve for suggestion.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summ column ... (2000)

    Hans is possible to use the same tecnique to add total in column N for each orizzontal range do until last cell in column A is filled?
    Example:
    summing range B5:M5 put total in cell in column N
    summing range B6:M6 put total in cell in column N
    ...
    summing range B69:M69 put total in cell in column N

    In this case the column of total is N but the sheet is dinamic...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summ column ... (2000)

    The problem is that the code cannot know whether the last filled column contains data or totals, so if you run the code twice, you'll get two columns with totals.
    If you want to use code, it would be better to place the column with totals to the left of the data instead of the the right, just like the other totals are above the data instead of below them.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summ column ... (2000)

    in each case the column to insert the total is where in line 4 is present the field named "TOT" (intercept this field name)...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summ column ... (2000)

    Try this version:

    Sub MakeSum()
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim lngRow As Long
    Dim lngCol As Long
    lngMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
    lngMaxCol = Range("4:4").Find(What:="TOT", LookIn:=xlValues, _
    LookAt:=xlWhole).Column
    ' Row totals
    For lngRow = 5 To lngMaxRow
    Cells(lngRow, lngMaxCol) = Application.WorksheetFunction.Sum( _
    Range(Cells(lngRow, 2), Cells(lngRow, lngMaxCol - 1)))
    Next lngRow
    ' Column totals
    For lngCol = 2 To lngMaxCol
    Cells(3, lngCol) = Application.WorksheetFunction.Sum( _
    Range(Cells(5, lngCol), Cells(lngMaxRow, lngCol)))
    Next lngCol
    End Sub

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summ column ... (2000)

    WIZARD....!!!!
    Tks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •