Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts

    How to have user add a line keeping formatting and average running correctly.

    So I need to track the average time it takes to dispatch out a call.

    using Excel 2010 See the attached file

    How do I make it so the user can add a line keeping the formatting and adding the new line to the average section.

    That way I do not need to create 100's of lines

    Any thoughts

    I FIGURED THIS ONE OUT THANKS FOR ALL YOU HELP
    Attached Files Attached Files
    Last edited by nsmjc1; 2015-10-26 at 09:05. Reason: SOLVED

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    nsmjc,

    You can change your formula to: =AVERAGEIF($F:$F,">0")
    You need only do the above once.

    Then just have the user:
    1. Click the last row number.
    2. Click the format painter.
    3. Click the first empty row number.
    4. Click the last entry in column F & drag down.
    5. Now enter the data.


    Of course, if you're not adverse to having VBA code in the work book a button could be added to automate those steps. Post back if this is not a problem and we'll get on it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Oct 2015
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    nsmjc,

    Of course, if you're not adverse to having VBA code in the work book a button could be added to automate those steps. Post back if this is not a problem and we'll get on it.

    HTH
    VBA would even be better than format painter if you knew a code for that I would be eternally grateful!

  4. #4
    New Lounger
    Join Date
    Oct 2015
    Posts
    9
    Thanks
    4
    Thanked 0 Times in 0 Posts
    So messing around with VBA I have come up with this code however it only creates a row with formatting. I have a couple of formulas in the row which are not being copied how would I get the formulas to copy over. The Formulas are in Cells G5 & H5.

    Private Sub CommandButton1_Click()

    Sheets("Time_Entry").Range("A6").Select
    ActiveCell.EntireRow.Insert Shift:=xlDown

    Sheets("Time_Entry").Range("B6:H6").Select
    Selection.Borders.Weight = xlThin

    Sheets("Time_Entry").Rows(6).RowHeight = 15

    End Sub

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    nsmjc,

    Give this a try: Avg Disp Time.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could also convert it to a Table and add a Totals row set to Average the time.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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