1. Win some Crucial goodies in OCAU's Christmas Treasure Hunt!
    Dismiss Notice

Excel- Easy way to apply same auto to whole table

Discussion in 'General Software' started by xc351, Dec 4, 2018.

  1. xc351

    xc351 Member

    Joined:
    Dec 30, 2005
    Messages:
    2,286
    Pretty much I’m doing up a spread sheet to keep track of costs.

    So every line has the same rules is there a way I can add the same autosum rule to all the coloums even unpopulated ones.


    If it helps I’m trying to work out cost to reload ammo.
     
  2. Tinian

    Tinian Member

    Joined:
    Jan 3, 2009
    Messages:
    15,328
    Location:
    15.0° N, 145.63° E
    copy paste?
     
  3. OP
    OP
    xc351

    xc351 Member

    Joined:
    Dec 30, 2005
    Messages:
    2,286
    Yea it’s what doing but every one needs to be adjusted for the rows eg h6*e6 to h7*e7.

    Each row has about 6 formulas
     
  4. philquad

    philquad Member

    Joined:
    Jan 22, 2004
    Messages:
    769
    Location:
    nelson bay
    you mean like this ?
    can you post me a demo Capture.JPG
     
  5. OP
    OP
    xc351

    xc351 Member

    Joined:
    Dec 30, 2005
    Messages:
    2,286
    I got it sorted. Pretty much a + arrow appears and you can drag it down. Double clicking it didn’t work

    Was just glitchy as anything with my adverage work laptop and a touchpad.

    Plus 1 or 2 issues were in a unassociated table i can put in current powder prices way over on the right. But I can do that manually.
     
  6. GTR27

    GTR27 Member

    Joined:
    Aug 16, 2002
    Messages:
    8,015
    Location:
    Sydney
    You can also highlight the last cell that the formula is in, then down to where you want it to go to and press CTRL+D to fill the formula down those cells.

    Double clicking when it shows the '+' only works if there is valid data in the column next to it (so it has some reference as to how far down to fill).
     
  7. elh9

    elh9 Member

    Joined:
    Feb 28, 2016
    Messages:
    104
    Location:
    Perth NOR
    Could do something like below VBA.

    Usage:
    1. Place it in the "ThisWorkbook" module.
    2. Set TotalColumn number on 3rd line to numeric value (A=1, B=2, etc)

    Every time you enter a value into a cell the total column will update, whether its a new row or not.
    ** There's probably a more efficient way to do this, but it works

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim TotalColumn As Integer: TotalColumn = 5
    Dim CurrentRow As Integer
    
    If Not Target.Column < TotalColumn Then
        GoTo SkipToEnd
    End If
    
    For CurrentRow = 1 To EndOfRowData
    
        Dim sumTarget As Range
        Dim result As Integer
        Set sumTarget = ActiveSheet.Range(Cells(CurrentRow, 1), Cells(CurrentRow, (TotalColumn - 1)))
    
        result = Application.Sum(sumTarget)
        ActiveSheet.Cells(CurrentRow, TotalColumn).Value = result
    
    Next CurrentRow
    
    SkipToEnd:
    
    End Sub
    
    Function EndOfRowData() As Long
    
    EndOfRowData = Application.ActiveSheet.Cells.SpecialCells(xlLastCell).Row
    
    End Function
    
     
    Last edited: Dec 11, 2018 at 4:01 PM
  8. MUTMAN

    MUTMAN Member

    Joined:
    Jun 27, 2001
    Messages:
    5,412
    Location:
    4109
    drag fill is your friend. where you have a cell you want to keep constant in the formula use the "$" sign

    say you are adding three cols in a group of rows;
    A,B, and C and one 'fixed' cell F
    A1+B1+C1+$F$1
    the next line when drag filled becomes
    A2+B2+C2+$F$1
    A3+B3+C3+F$1$
    the $ stops the drag fill from moving that cell down the rows

    hope that makes sense
     

Share This Page