Looking for Inspiration: Attendance reporting/matching

Hello Community, I am not sure where to start with this project: I have a class attendance sheet that I'd like to build further and I'm not sure where to go with it.

Here are the parameters: I have several groups of participants (cast members for various entertainment productions). Each group has different numbers, as they have different roles; each role then gets assigned to one of 3 classes for physical training.

Class participants scan a QR code each day, which takes them to a smartsheet form and allows them to "check in" for that day. I can generate a daily attendance report but this is only names and numbers - i'd like to be able to match these partipants to their original group, so I automatically know who was there and who was not, and whether they were in the right class.

I asked my good friend Claude.ai and came up with a way to do this with VLOOKUP, INDEX MATCH etc - i haven't built it yet in excel. Is there a way to do something like this in smartsheet?

Find more posts tagged with

Forms

Reports

Workflows in Smartsheet

Accepted answers

jmyzk_cloudsmart_jp

Hi @jmaxbr

Great question! Your question made me think a lot. Here is my demo solution.

First, I created a Member Table, which lists members and maps them to groups as shown below.

https://app.smartsheet.com/b/publish?EQBCT=5fffa19be7dc4a7d865449b7fece47d9

I added the [Member Group Pattern] for an easy reference to a member's group belonging pattern with this formula.

=IF([G1]@row , "1", "0") + IF([G2]@row , "1", "0") + IF([G3]@row , "1", "0") + IF([G4]@row , "1", "0") + IF([G5]@row , "1", "0") + IF([G6]@row , "1", "0") + IF([G7]@row , "1", "0")

For example, Alice in the first row belongs to G1, G2, and G3 among the seven groups. So, the pattern is 1110000.

Second, I created the Class-Group Mapping sheet, as shown below, to determine which group should attend what classes.

https://app.smartsheet.com/b/publish?EQBCT=2ab3c922f21e47a48250352406894516

Again, I added the [Class Group Pattern] helper column for easy reference to show the group pattern with the same formula as above.

Third, I created the Class 1 sheet as shown below.

https://app.smartsheet.com/b/publish?EQBCT=58151e6e5ac74c4fb2b614f6061ad67b

The form input will create the first three columns.

The form is a check box only and requires the user to log in to Smartsheet for easy member recognition via the Created By system column.　(For the demo purpose, I have an Email column, which I input from the member list manually.)

To avoid requiring members to log in to Smartsheet, for example, you'll need to add a member list dropdown.

Among the following five columns, the first gets the class name from the Sheet Summary fields.

The other four columns get information from the Member Table using the Email@row as a key in the VLOOKUP or INDEX(MATCH()) formulas, and the Class# as a key to get the class's group pattern from the Class-Group Mapping sheet.

[Class] =Class#
[Name] =IFERROR(VLOOKUP(Email@row , {Members_Table Range: Email to G10}, 2), "")
[Member Number] =IFERROR(VLOOKUP(Email@row , {Members_Table Range: Email to G10}, 3), "")
[Member Group Pattern] =IFERROR(INDEX({Members Table : Group Pattern}, MATCH(Email@row , {Members Table : Email}, 0)), "")
[Class Group Pattern] =IFERROR(INDEX({Class - Group Mapping : Group Pattern}, MATCH(Class#, {Class - Group Mapping: Class}, 0)), "")

Finally, the [Correct] column determines if a member's class attendance is correct by comparing the group pattern of the member and the class with the following formula;

=OR(
  IF(MID([Class Group Pattern]@row, 1, 1) = 1, 
    MID([Class Group Pattern]@row, 1, 1) = MID([Member Group Pattern]@row, 1, 1), false), 
  IF(MID([Class Group Pattern]@row, 2, 1) = 1, 
    MID([Class Group Pattern]@row, 2, 1) = MID([Member Group Pattern]@row, 2, 1), false), 
  IF(MID([Class Group Pattern]@row, 3, 1) = 1, 
    MID([Class Group Pattern]@row, 3, 1) = MID([Member Group Pattern]@row, 3, 1), false), 
  IF(MID([Class Group Pattern]@row, 4, 1) = 1, 
    MID([Class Group Pattern]@row, 4, 1) = MID([Member Group Pattern]@row, 4, 1), false), 
  IF(MID([Class Group Pattern]@row, 5, 1) = 1, 
    MID([Class Group Pattern]@row, 5, 1) = MID([Member Group Pattern]@row, 5, 1), false), 
  IF(MID([Class Group Pattern]@row, 6, 1) = 1, 
    MID([Class Group Pattern]@row, 6, 1) = MID([Member Group Pattern]@row, 6, 1), false), 
  IF(MID([Class Group Pattern]@row, 7, 1) = 1, 
    MID([Class Group Pattern]@row, 7, 1) = MID([Member Group Pattern]@row, 7, 1), false)
)

For example, the Class 1 group pattern is '1110000.' So, the first IF clause checks if G1 or group1 is required to attend the class.

 IF(MID([Class Group Pattern]@row , 1, 1) = 1

If so, the following checks if the member belongs to G1. If so, the formula returns true, and as we have all those IF formulas in the OR function, the [Correct] column will be checked.

MID([Class Group Pattern]@row , 1, 1) = MID([Member Group Pattern]@row , 1, 1)

Since G4 to G7 are not required to attend this class according to the group pattern '1110000', the fourth IF clause is not valid; therefore, we put 'false' in that case.

IF(MID([Class Group Pattern]@row, 4, 1) = 1,

In the case of Diana, in the fifth row, the group pattern is 0000111, which means she belongs to G5, G6, and G7.

As none of the group is required to attend this class, the [Correct] is unchecked, and the conditional format warns of the wrong attendance.

The G1 to M7 columns are not necessary, but I added them to check Group Patterns against the real group setting.

[G7] =IFERROR(VLOOKUP(Email@row , {Members_Table Range: Email to G10}, 10), "")
[M1] =IFERROR(VLOOKUP(Class@row , {Class - Group Mapping Range : Class to G10}, 2), "")

With all columns, formulas, and the Sheet Summary field setups in place, we can copy the sheet to create one for Classes 2 and 3. However, you may want to add a workflow automation, like the one below, to copy all the rows to one master sheet for future use.

The report below shows the member's check in detail by class.

https://app.smartsheet.com/b/publish?EQBCT=f3c2c4a9f5344f51ac846d20352b8688

Regarding the "who was there and who was not", I created three additional sheets and a report.

First, Class # Member: This Week Check In sheet lists the class members and their check-in status for the current week.

https://app.smartsheet.com/b/publish?EQBCT=186a4cd4bb064c51acb9cda7dc960f0d

The [Include] column determines the member that belongs to the class by this formula, which uses the group pattern information;

OR(
  IF(MID([Class Group Pattern]#, 1, 1) = 1, 
    MID([Class Group Pattern]#, 1, 1) = MID([Member Group Pattern]@row , 1, 1), false), 
  IF(MID([Class Group Pattern]#, 2, 1) = 1, 
    MID([Class Group Pattern]#, 2, 1) = MID([Member Group Pattern]@row , 2, 1), false), 
  IF(MID([Class Group Pattern]#, 3, 1) = 1, 
    MID([Class Group Pattern]#, 3, 1) = MID([Member Group Pattern]@row , 3, 1), false), 
  IF(MID([Class Group Pattern]#, 4, 1) = 1, 
    MID([Class Group Pattern]#, 4, 1) = MID([Member Group Pattern]@row , 4, 1), false), 
  IF(MID([Class Group Pattern]#, 5, 1) = 1, 
    MID([Class Group Pattern]#, 5, 1) = MID([Member Group Pattern]@row , 5, 1), false), 
  IF(MID([Class Group Pattern]#, 6, 1) = 1, 
    MID([Class Group Pattern]#, 6, 1) = MID([Member Group Pattern]@row , 6, 1), false), 
  IF(MID([Class Group Pattern]#, 7, 1) = 1, 
    MID([Class Group Pattern]#, 7, 1) = MID([Member Group Pattern]@row , 7, 1), false)
)

The group patterns are retrieved with the following formula;

[Member Group Pattern] =INDEX({Members Table : Group Pattern}, Index@row )

[Class Group Pattern]#  (A sheet Summary Field)
  =INDEX({Class - Group Mapping : Group Pattern}, MATCH(Class#, {Class - Group Mapping : Class}, 0))

This formula obtains name and Member Emails;

[Name] =IF(Include@row , INDEX({Members Table : Name}, Index@row ))
[Member Email] =IF(Include@row , INDEX({Members Table : Email}, Index@row ))

Finally, [This Week Check In] is determined by the following formula;

[This Week Check In] 
  =IFERROR(
    INDEX(
      COLLECT(
        {Class Check In Master Sheet : Check In}, 
          Include:Include, true, 
          {Class Check In Master Sheet : Email}, [Member Email]@row ,
          {Class Check In Master Sheet : Created}, WEEKNUMBER(@cell ) = WEEKNUMBER(TODAY())
            ), 
        1)
  , "")

I used the Class Check In Master Sheet because I wanted to copy and duplicate it for Classes 2 and 3. By referencing the master sheet, we do not have to create cross-sheet references for each sheet.

So, the formula gets the check in status from the master sheet, of the members included in the class, and the email is in the master sheet, and the Created Data in the master sheet is of the current week.

Combining the three sheets thus created, and grouping by class, we get the 'Class Member: This Week Check In' report, as shown below.

https://app.smartsheet.com/b/publish?EQBCT=ad4b8b323a6d4e14a203946c5499afa6

The solution is somewhat complex and utilizes numerous formulas and cross-sheet references.😅

If you need a copy of the solution, please get in touch with me by email on my profile page.

jmyzk_cloudsmart_jp

@jmaxbr

Get the Form's URL and convert the QR Code with any converter.

Google 'QR code generator'

Example;

https://www.qr-code-generator.com/

All comments

Naeem Ejaz

Try this template "Team PTO and Absence Management" maybe it guides you towards your use case with some modifications:

https://app.smartsheet.com/r/template-gallery/templates/b75b5bcc-7d8f-4fd6-b5d3-9a909566eb04/Team-PTO-%26-Absence-Management?tg-locale=en_US

jmyzk_cloudsmart_jp

Hi @jmaxbr

Great question! Your question made me think a lot. Here is my demo solution.

First, I created a Member Table, which lists members and maps them to groups as shown below.

https://app.smartsheet.com/b/publish?EQBCT=5fffa19be7dc4a7d865449b7fece47d9

I added the [Member Group Pattern] for an easy reference to a member's group belonging pattern with this formula.

=IF([G1]@row , "1", "0") + IF([G2]@row , "1", "0") + IF([G3]@row , "1", "0") + IF([G4]@row , "1", "0") + IF([G5]@row , "1", "0") + IF([G6]@row , "1", "0") + IF([G7]@row , "1", "0")

For example, Alice in the first row belongs to G1, G2, and G3 among the seven groups. So, the pattern is 1110000.

Second, I created the Class-Group Mapping sheet, as shown below, to determine which group should attend what classes.

https://app.smartsheet.com/b/publish?EQBCT=2ab3c922f21e47a48250352406894516

Again, I added the [Class Group Pattern] helper column for easy reference to show the group pattern with the same formula as above.

Third, I created the Class 1 sheet as shown below.

https://app.smartsheet.com/b/publish?EQBCT=58151e6e5ac74c4fb2b614f6061ad67b

The form input will create the first three columns.

To avoid requiring members to log in to Smartsheet, for example, you'll need to add a member list dropdown.

Among the following five columns, the first gets the class name from the Sheet Summary fields.

[Class] =Class#
[Name] =IFERROR(VLOOKUP(Email@row , {Members_Table Range: Email to G10}, 2), "")
[Member Number] =IFERROR(VLOOKUP(Email@row , {Members_Table Range: Email to G10}, 3), "")
[Member Group Pattern] =IFERROR(INDEX({Members Table : Group Pattern}, MATCH(Email@row , {Members Table : Email}, 0)), "")
[Class Group Pattern] =IFERROR(INDEX({Class - Group Mapping : Group Pattern}, MATCH(Class#, {Class - Group Mapping: Class}, 0)), "")

Finally, the [Correct] column determines if a member's class attendance is correct by comparing the group pattern of the member and the class with the following formula;

=OR(
  IF(MID([Class Group Pattern]@row, 1, 1) = 1, 
    MID([Class Group Pattern]@row, 1, 1) = MID([Member Group Pattern]@row, 1, 1), false), 
  IF(MID([Class Group Pattern]@row, 2, 1) = 1, 
    MID([Class Group Pattern]@row, 2, 1) = MID([Member Group Pattern]@row, 2, 1), false), 
  IF(MID([Class Group Pattern]@row, 3, 1) = 1, 
    MID([Class Group Pattern]@row, 3, 1) = MID([Member Group Pattern]@row, 3, 1), false), 
  IF(MID([Class Group Pattern]@row, 4, 1) = 1, 
    MID([Class Group Pattern]@row, 4, 1) = MID([Member Group Pattern]@row, 4, 1), false), 
  IF(MID([Class Group Pattern]@row, 5, 1) = 1, 
    MID([Class Group Pattern]@row, 5, 1) = MID([Member Group Pattern]@row, 5, 1), false), 
  IF(MID([Class Group Pattern]@row, 6, 1) = 1, 
    MID([Class Group Pattern]@row, 6, 1) = MID([Member Group Pattern]@row, 6, 1), false), 
  IF(MID([Class Group Pattern]@row, 7, 1) = 1, 
    MID([Class Group Pattern]@row, 7, 1) = MID([Member Group Pattern]@row, 7, 1), false)
)

For example, the Class 1 group pattern is '1110000.' So, the first IF clause checks if G1 or group1 is required to attend the class.

 IF(MID([Class Group Pattern]@row , 1, 1) = 1

If so, the following checks if the member belongs to G1. If so, the formula returns true, and as we have all those IF formulas in the OR function, the [Correct] column will be checked.

MID([Class Group Pattern]@row , 1, 1) = MID([Member Group Pattern]@row , 1, 1)

Since G4 to G7 are not required to attend this class according to the group pattern '1110000', the fourth IF clause is not valid; therefore, we put 'false' in that case.

IF(MID([Class Group Pattern]@row, 4, 1) = 1,

In the case of Diana, in the fifth row, the group pattern is 0000111, which means she belongs to G5, G6, and G7.

As none of the group is required to attend this class, the [Correct] is unchecked, and the conditional format warns of the wrong attendance.

The G1 to M7 columns are not necessary, but I added them to check Group Patterns against the real group setting.

[G7] =IFERROR(VLOOKUP(Email@row , {Members_Table Range: Email to G10}, 10), "")
[M1] =IFERROR(VLOOKUP(Class@row , {Class - Group Mapping Range : Class to G10}, 2), "")

The report below shows the member's check in detail by class.

https://app.smartsheet.com/b/publish?EQBCT=f3c2c4a9f5344f51ac846d20352b8688

Regarding the "who was there and who was not", I created three additional sheets and a report.

First, Class # Member: This Week Check In sheet lists the class members and their check-in status for the current week.

https://app.smartsheet.com/b/publish?EQBCT=186a4cd4bb064c51acb9cda7dc960f0d

The [Include] column determines the member that belongs to the class by this formula, which uses the group pattern information;

OR(
  IF(MID([Class Group Pattern]#, 1, 1) = 1, 
    MID([Class Group Pattern]#, 1, 1) = MID([Member Group Pattern]@row , 1, 1), false), 
  IF(MID([Class Group Pattern]#, 2, 1) = 1, 
    MID([Class Group Pattern]#, 2, 1) = MID([Member Group Pattern]@row , 2, 1), false), 
  IF(MID([Class Group Pattern]#, 3, 1) = 1, 
    MID([Class Group Pattern]#, 3, 1) = MID([Member Group Pattern]@row , 3, 1), false), 
  IF(MID([Class Group Pattern]#, 4, 1) = 1, 
    MID([Class Group Pattern]#, 4, 1) = MID([Member Group Pattern]@row , 4, 1), false), 
  IF(MID([Class Group Pattern]#, 5, 1) = 1, 
    MID([Class Group Pattern]#, 5, 1) = MID([Member Group Pattern]@row , 5, 1), false), 
  IF(MID([Class Group Pattern]#, 6, 1) = 1, 
    MID([Class Group Pattern]#, 6, 1) = MID([Member Group Pattern]@row , 6, 1), false), 
  IF(MID([Class Group Pattern]#, 7, 1) = 1, 
    MID([Class Group Pattern]#, 7, 1) = MID([Member Group Pattern]@row , 7, 1), false)
)

The group patterns are retrieved with the following formula;

[Member Group Pattern] =INDEX({Members Table : Group Pattern}, Index@row )

[Class Group Pattern]#  (A sheet Summary Field)
  =INDEX({Class - Group Mapping : Group Pattern}, MATCH(Class#, {Class - Group Mapping : Class}, 0))

This formula obtains name and Member Emails;

[Name] =IF(Include@row , INDEX({Members Table : Name}, Index@row ))
[Member Email] =IF(Include@row , INDEX({Members Table : Email}, Index@row ))

Finally, [This Week Check In] is determined by the following formula;

[This Week Check In] 
  =IFERROR(
    INDEX(
      COLLECT(
        {Class Check In Master Sheet : Check In}, 
          Include:Include, true, 
          {Class Check In Master Sheet : Email}, [Member Email]@row ,
          {Class Check In Master Sheet : Created}, WEEKNUMBER(@cell ) = WEEKNUMBER(TODAY())
            ), 
        1)
  , "")

I used the Class Check In Master Sheet because I wanted to copy and duplicate it for Classes 2 and 3. By referencing the master sheet, we do not have to create cross-sheet references for each sheet.

Combining the three sheets thus created, and grouping by class, we get the 'Class Member: This Week Check In' report, as shown below.

https://app.smartsheet.com/b/publish?EQBCT=ad4b8b323a6d4e14a203946c5499afa6

The solution is somewhat complex and utilizes numerous formulas and cross-sheet references.😅

If you need a copy of the solution, please get in touch with me by email on my profile page.

jmaxbr

This is outstanding, thank you so much. I'm going to try to make this work.

jmyzk_cloudsmart_jp

Happy to help!😁 @jmaxbr

jmaxbr

I'm not sure I need it but how did you create the QR code from the Sheet Summary? I already have a QR code and a separate sign-in sheet for various classes so I think I can link those, but it's certainly easier to do right from one sheet.

jmyzk_cloudsmart_jp

@jmaxbr

Get the Form's URL and convert the QR Code with any converter.

Google 'QR code generator'

Example;

https://www.qr-code-generator.com/

jmaxbr

Thanks, was thinking perhaps it was a feature I didn't know about ;)

jmaxbr

Thanks, I sent you an email!

I am still working on this and trying to make it happen based on what I have in place already. My class members scan a QR code and fill out a quick form, that is what tells me all their info and which class they attended each day.

From there, I am unsure how to "merge" the information that ends up in a raw "master" check-in sheet, with their particular member info - what their job code is, what class they should or should not be in.

jmyzk_cloudsmart_jp

I could not find your email.

Make sure to send to jmiyazaki@cloudsmart.jp.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.