+ Reply to Thread
Results 1 to 11 of 11

Formula to find inactive employees

  1. 12-25-2014, 03:35 PM #1
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Formula to find inactive employees

    Hello:

    please refer to attached sheet.
    I have monthly payroll info for lots of employees and some are in the attached sheet.
    I need to figure out the current inactive employees.
    i would select the start month and end month from drop down list in cell A17 and B17.
    So lets say i have selected A17 as Sep-14 and B17 as Nov-14.
    I need a formula to list in cell A20 download so that if any employee listed in row 1 between these months
    have zero payroll will be treated as inactive.
    I have manually entered inactive employee as shown in cell A19 downwards.

    Please let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-25-2014, 04:02 PM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to find inactive employees

    Use a helper row..

    See attached.. formulas highlighted in green
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 12-25-2014, 04:27 PM #3
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to find inactive employees

    Hi Ace_XL:

    Very smart, thanks a lot for great work.
    Merry XMas

    Riz
    Reply With QuoteRegister To Reply
  4. 12-25-2014, 04:40 PM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to find inactive employees

    In A19 and copy down

    =IFERROR(INDEX($B$1:$H$1,SMALL(IF((Month>$A$17)*(Month<$B$17)*($B$2:$H$13=0),COLUMN($B$2:$H$13)-COLUMN(B2)+1),ROWS(A$1:A1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 12-26-2014, 11:31 AM #5
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to find inactive employees

    Hi Alkey:

    Thanks a lot.
    Works great
    Riz
    Reply With QuoteRegister To Reply
  6. 12-26-2014, 03:08 PM #6
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to find inactive employees

    Hello Alkey:

    I am trying to use the formula in my situation and i am liitle lost.
    Please refer to attahched sheet and go to sheet2.
    The data is shown and need the above formula in cell L35.
    please help and let me know if any questions.
    Thanks a lot.
    Riz
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 12-26-2014, 05:40 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to find inactive employees

    Hi Riz,

    Please see revised formula on Sheet2. Please note that I changed the named range on Sheet2 to Month2

    Please see attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 12-26-2014, 06:07 PM #8
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to find inactive employees

    Hello Alkey:

    Please refer to attached file.
    I have extended the data and I have change the formula in cell L35 to reflect the increase in data but for some reason the formula does not give correct result.
    Please help and check the formula and see if i have error in the error.
    Let me know if you have any questions.
    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 12-26-2014, 06:21 PM #9
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to find inactive employees

    Formula didn't work because you did not adjust ranges.

    Please see attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 12-26-2014, 06:23 PM #10
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to find inactive employees

    Hi Alkey:

    Thanks a lot...
    I see it, works great.
    Thanks once again.
    Riz
    Reply With QuoteRegister To Reply
  11. 12-26-2014, 06:26 PM #11
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to find inactive employees

    You'r welcome and thank you for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Forecast How Many Employees To Hire Based on Active and Termed Employees
    By Kanook22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2014, 01:19 PM
  2. Find employees available to work this shift?
    By jhiltabidel in forum Excel General
    Replies: 2
    Last Post: 03-28-2013, 08:19 PM
  3. Employee Tracking spreadsheet of 500 employees with 30 trainings required by employees.
    By gg0690 in forum Excel General
    Replies: 2
    Last Post: 03-11-2013, 09:59 PM
  4. find the last used column on an INACTIVE sheet
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2008, 09:23 AM
  5. VBA: Using the Find Method on an inactive sheet
    By BigBas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-18-2007, 06:49 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1