+ Reply to Thread
Results 1 to 7 of 7

Attendance Form

  1. #1
    Registered User
    Join Date
    10-11-2015
    Location
    Connecticut
    MS-Off Ver
    Office 365
    Posts
    3

    Attendance Form

    I am attempting to create an attendance sheet that will transpose all training classes from a primary tab with all classes listed and each individuals attendance, to their own individual tab. For example employee A and B attend continuing education class X, but employee A attends class y individually as well. I would like Class Name, date of class, and hours in attendance to transfer over to each employees tab for only the classes they attended not the ones they did not. Any help is greatly appreciated Thanks in Advance.

    FD Training.xlsx
    Last edited by cfdff86; 10-11-2015 at 12:54 PM. Reason: Added Attachment

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Attendance Form

    with a small vba code in workbook sheet activate


    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Attendance Form

    This ARRAY formula will give you what you want....
    =IFERROR(INDEX(Before!A:A,SMALL(IF(Before!$D$3:$D$8="x",ROW(Before!$A$3:$A$8)),ROWS($A$1:A1)),MATCH($A$1,Before!$D$1:$E$1,0)),"")
    ...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.
    copied down and across

    You will need to change the BOLDED range for each column, but I am working on a way around that
    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

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Attendance Form

    OK, try this ARRAY formula, no need to make adjustments...
    =IFERROR(INDEX(Before!A:A,SMALL(IF(INDEX(Before!$D$3:$E$8,0,MATCH($A$1,Before!$D$1:$E$1,0))="x",ROW(Before!$A$3:$A$8)),ROWS(Before!$A$1:A1))),"")
    Copy down and across as needed for all sheets

  5. #5
    Registered User
    Join Date
    10-11-2015
    Location
    Connecticut
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Attendance Form

    Quote Originally Posted by LeoTaxi View Post
    with a small vba code in workbook sheet activate


    Kind regards
    Leo
    Thanks Leo,

    This works for me the only issue I am having as I try and add new names and their sheets I get a mismatch error in line 9 of the vba code and i can't correct it. I don't believe the sheet is actively adding new names as new sheets. Is there any way to change the coding?

  6. #6
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Attendance Form

    sorry, i like to search and play with excel
    but it stops if i have to wait a few months for an answer.

    Kind regards
    Leo

  7. #7
    Registered User
    Join Date
    10-11-2015
    Location
    Connecticut
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Attendance Form

    Absolutely understandable. Thanks for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 10
    Last Post: 03-17-2016, 05:34 AM
  2. Need Help - AutoCalculate Attendance Points for employee attendance records
    By switzd0d in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-17-2015, 03:00 PM
  3. Holiday / Attendance Form
    By budvegas in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 12-23-2011, 04:13 AM
  4. attendance log
    By excellionaire in forum Excel General
    Replies: 20
    Last Post: 08-16-2011, 02:48 PM
  5. Replies: 3
    Last Post: 06-12-2011, 02:56 PM
  6. Attendance Log
    By mwilburn01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-14-2010, 11:31 AM
  7. Filling form fields via worksheet dropdowns, user update via form, change form color
    By Demosthenes&Locke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2010, 08:58 AM

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