+ Reply to Thread
Results 1 to 6 of 6

How to identify absent employees across 2 worksheets with results on a 3rd worksheet

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question How to identify absent employees across 2 worksheets with results on a 3rd worksheet

    Hello,

    So, I need to track my employee's attendance. On the Summary tab I would like for their name to populate if they are scheduled to work that day (see Schedule 1 or Schedule 2 tabs) and they do not scan into work (see tabs Monday - Saturday) therefor making them absent. I would love if it was possible to use the Schedule 1 tab to do this as I am currently using this format as our scheduling tool.

    I have tried using conditional formatting and several formulas (OR, MATCH, COUNTIF) but with no luck. Can anyone please point me in the right direction?
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: How to identify absent employees across 2 worksheets with results on a 3rd worksheet

    Hi and welcome to the forum

    Im looking at your file, it would help if you included a few sample answers to show what you want

    On schedile1 sheet, is there any significance (from your question point of view) of the different letters?

    Also in your CF rules, under "applies to", you can use =$C$7:$AF$18 instead of the "mess" you are currently using
    (=$C$7:$D$8,$AE$7:$AF$17,$F$8:$L$17,$E$7:$L$7,$C$18:$L$18,$M$7:$AD$18,$C$9:$E$17)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-26-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to identify absent employees across 2 worksheets with results on a 3rd worksheet

    Hi! and Thank you!

    The only significance of the letters in Schedule 1 tab is who is scheduled to work that day (the different letters are to identify what they are scheduled to do).

    What I would like is: under the Attendance area on the Summary tab, I would like for all absent employees to appear under the day that they were supposed to be there.

    For example: Both Rosabel and Greg should have been working on Tuesday (according to Schedule 1), but neither showed up on the Tuesday tab. I would like both of their names to show under "Tuesday" on the Summary tab marking them as absent.

    Thank you for all of your help and the tip on the CF!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: How to identify absent employees across 2 worksheets with results on a 3rd worksheet

    See if the attached is what you want?

    I noticed that you have mon-sun on sched1, but only mon-sat sheets

    I added helper columns on each weekday sheet, you can hide them if you want
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-26-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to identify absent employees across 2 worksheets with results on a 3rd worksheet

    Oh my gosh, that is perfect! Thank you so much!

    The only thing that I changed was the font color on the helper columns from black to white, and added the Sunday sheet. Also, instead of the VLOOKUP range being on each sheet referencing each employee with their badge numbers, I created a master list of badge numbers on another sheet.

    Thank you SO much!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: How to identify absent employees across 2 worksheets with results on a 3rd worksheet

    Happy to help and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1