+ Reply to Thread
Results 1 to 11 of 11

Create Attendance Report using Lists of Participants

  1. #1
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Create Attendance Report using Lists of Participants

    I need to create an attendance report where there is almost 200 participants to record. The spreadsheet I have set up has a tab for attendance data (a list of names who attended in a single column with the date as a header) and a tab for the report. What I would like to do is have a formula on the report tab that looks at the data tab (finding the column for the matching date) and put the word "There" in the row/column next to their name.

    I have attached a small sample workbook with a section for the expected results. In the real workbook, I will be creating this report each month and the list of participants will change (along with the number of participants).

    Thank you for any assistance you can give me with this.

    Cliff
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Create Attendance Report using Lists of Participants

    In C3 enter =IF(ISERROR(VLOOKUP($B3,'Attendance Data'!B$3:B$30,1,0)),"", "There") drag the formula across and down

  3. #3
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Create Attendance Report using Lists of Participants

    Thank you!!! I will plug this in later today and see what happens in the "real world."

    Thank you again!!!

  4. #4
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Create Attendance Report using Lists of Participants

    Welllll... I made my sample workbook too simple. I have multiple columns for each date, which is why I need to be able to match the date instead of just copying the formula across. I changed JieJenn's formula to an index-match so I could match the date - but my formula for the attendance data column is hard coded... I need to be able to find the column so when I copy/paste this formula across the sheet, it will look in the right place for the correct attendance data. Here is what I changed the formula to (in R3 - copied down):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here is my problem section: MATCH(B3,'Attendance Data'!D:D,0)

    I have attached an updated sample workbook which better emulates the environment I am working in.

    Thank you again for your assistance!!!!
    Attached Files Attached Files

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Create Attendance Report using Lists of Participants

    Quote Originally Posted by CWatsonJr View Post
    =IF(ISERROR(INDEX('Attendance Data'!$B:$F,MATCH(B3,'Attendance Data'!D:D,0),MATCH(O$1,'Attendance Data'!$B$2:$F$2,0))),"", "There")

    Here is my problem section: MATCH(B3,'Attendance Data'!D:D,0)
    Why is that a problem?

    I downloaded your file but I'm not sure what you're wanting to do.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Create Attendance Report using Lists of Participants

    If I copy/paste the formula across the sheet (as it is now), it will pick up the wrong columns (on the data tab) because I have the extra columns on the report tab.


    Sent from my iPad using Tapatalk

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Create Attendance Report using Lists of Participants

    So, you only want the formulas in the "Att" columns?

    What are the Col 1, Col 2 and Col 3 columns for?

    Another "problem" is the date headers are merged cells!

  8. #8
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Create Attendance Report using Lists of Participants

    That is correct.

    The col 1, col 2, and col 3 columns are for recording other data (like speakers, classes, etc. - but there isn't as many data entries in those columns as there is in the Att column so I just hand enter them).

    The merged cells are not a problem, I just pick the first column of the merged cell for the date match.

    Thank you!


    Sent from my iPad using Tapatalk

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Create Attendance Report using Lists of Participants

    OK, you won't be able to drag copy the formula across the columns. You'll have to copy/paste into each of the "Att" columns.

    Enter this formula in F3:

    =IF(ISNUMBER(MATCH($B3,INDEX('Attendance Data'!$B:$F,0,MATCH(C$1,'Attendance Data'!$B$2:$F$2,0)),0)),"There","")

    Drag copy down to F24.

    Then, select cell F3. Copy it and paste into cell J3.

    Drag copy down to J24.

    Repeat this process for all of the "Att" columns.

  10. #10
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Create Attendance Report using Lists of Participants

    This works perfectly! Thank you.

    Now - I will apply it to the real workbook and see if I can work out any kinks there may be.

    *************************************************

    This worked perfectly in the real world workbook!!!

    Thank you again!!!

    ********************************************
    Last edited by CWatsonJr; 05-28-2015 at 09:04 PM. Reason: Update Reply

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Create Attendance Report using Lists of Participants

    You're welcome. 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)

Similar Threads

  1. [SOLVED] ATTENDANCE REPORT templete
    By Sundarvlr in forum Excel General
    Replies: 2
    Last Post: 11-25-2013, 05:58 PM
  2. Convert Detailed Time Attendance report from Biometrics to Summary Report
    By firescorpio in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2013, 02:48 AM
  3. Replies: 4
    Last Post: 07-19-2013, 07:48 PM
  4. URGENTLY need a code to create a Attendance report.
    By raghavnrrp in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-14-2012, 06:17 AM
  5. Form Email from Report, Only if Report summary sheet lists Acct for that Sales Rep
    By lukep10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2008, 01:38 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1