Forum Discussion

Lisa Anderson's avatar
Lisa Anderson
Copper Contributor
Apr 02, 2018

Adding Hours on a Schedule Spreadsheet

Hi. I'm hoping someone can help me with this issue. I currently have small side job that I schedule and calculate payroll in Excel. I've been trying to automate the spreadsheet as much as possible but I'm currently stuck at how to get excel to look for each employee's name and then grab/add the hours next to them name and dump the total in the bottom (highlighted yellow). 

 

For example, I need a function to search for "Gary", find his name 2 times, pull the hours (6 each time) and then add the hrs and put a 12 next to his name in the bottom. Currently I am manually doing this and it's time consuming and subject to errors.

 

Can anyone help? I've attached the spreadsheet. Please let me know if you have questions and I'll try to answer them. I didn't create this spreadsheet, I've just been trying to improve it so I won't be offended if you say it needs a total overhaul! 

 

 

Formulas & Functions
Need Help

1 Reply

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Apr 02, 2018

    Lisa-

     

    I have created a User Defined Function (UDF) that will help you.   In order to use it you will need to follow these steps:

     

    1. Use Key Combination Alt + F11 to access the Visual Basic Editor

    2. Go To Insert > Module

    3. Paste this code in the window that appears:

     

    Function GetEEHours(SearchFor As Range, SearchIn As Range) As Single

    Dim fnd As String
    Dim cll As Range
    Dim Total As Single
    
    If SearchFor.Count > 1 Then Exit Function
    
    fnd = SearchFor.Value
    
    For Each cll In SearchIn
        If cll.Value = fnd Then
            Total = Total + cll.Offset(, -1).Value
        End If
    Next cll
    
    GetEEHours = Total
 
End Function

    4. Save file as Macro Enabled Workbook

    5. Use this Syntax with the new UDF =GetEEHours(SearchFor, SearchIn)

     

    i.e. In Cell E15 the formula looks like this: =GetEEHours(A15,$A$1:$AB$13)

     

    The UDF basically searches the range you provide for the word "Eric" (Cell A15) then when it finds the word Eric it looks to the Left of it to get the hours they are working....then it goes to the next one...and so forth.

     

    I'm attaching a workbook with the syntax.  However, you will need to insert the code as mentioned above.  This forum does not allow me to attach a macro enabled files (Files with Code).

     

     

     

     

     

Resources