Comparing Data and Creating a report from multiple sheets

Jason1000 · 03/06/25

I feel confident there's a way to do this, I just haven't figured out the correct combination yet. What I have is an MS OneDrive Excel sheet with a roster of all employees. Then I have a SS w/ an attendance form that each employee completes weekly for safety meetings. Currently I have to manually compare the list of names from the SS attendance form to the Excel roster. How can I automate this to generate a report that shows the names that are on the roster, but not on the attendance form, limiting the results within the last 7 days, run once a week.

Also, I should note that the names on the SS attendance form are a dropdown list updated from the excel roster by a data shuttle, so entries will match the roster exactly.

Thanks in advance for suggestions

Georgie · 03/12/25

Hi @Jason1000,

I’d do this using MAX(COLLECT) to find the latest sign in date for each employee, and then see if that date is within the last 7 days using <= TODAY(-7).

The formula I was able to get working in my test sheets is as follows:

=IFERROR(IF(OR(MAX(COLLECT({Source | Date}, {Source - Primary Column}, [Primary Column]@row)) <= TODAY(-7), MAX(COLLECT({Source | Date}, {Source - Primary Column}, [Primary Column]@row)) = ""), "Not Signed In", "Signed In"), "Not Signed In")

The cross sheet references are as follows:

{Source | Date} is the entire Sign in Date column in the source sheet
{Source - Primary Column} is the entire Primary Column in the source sheet

You can (and I recommend doing so) rename your sheet references as desired so that you understand what your formulas reference if/when you come back to them later.

The formula says: If the latest sign in date for the employee in this row is over 7 days prior to today, or there’s no latest sign in date for this employee (the name is present in the source sheet but has no sign in date), return “Not Signed In”. If the latest sign in date for the employee in this row is within the last 7 days, return “Signed In”, and if there’s an error (meaning the name is not in the source sheet), return “Not Signed In”).

My source sheet can be seen below:

Below is my destination sheet and you can see the formula working here:

That is, it shows “Not Signed In” for names 1 and 3 because their latest sign in date is not within the last 7 days of today (12th March), and it shows “Not Signed In” for names 9 to 13 because those names are not present in the source sheet, meaning they haven’t signed in at all.

Please note that when using the TODAY function, you'll need to ensure the sheet with the formula is opened and saved daily, or use the suggestions in this help article to automate this process: Automatically update the TODAY function in formulas.

Does that work for you?

Georgie

Jgorsich · 03/06/25

I'd copy the list of employees into a SS from the Excel, then use a countifs() checking for the employee name in the list and the date being within the last 7 days against the output from your attendance form - those that toss a zero skipped attendance.

Jason1000 · 03/07/25

https://community.smartsheet.com/discussion/comment/475719#Comment_475719

Thanks for the suggestion, I'll give that a try and see if I get results I'm looking for.

Jason1000 · 03/11/25

Ok, still struggling with this. I ended up with this formula:

=IF(ISERROR(MATCH([Primary Column]@row, {Toolbox Talk sign in Range 1}, 0)), "Not Signed In", "Signed In")

which results in this:

The issue I'm having now is that I need this to be relevant to the week. I only want to look at names that do not match between the roster and the sign in sheet over the last 7 days. The same sign in sheet is filled in week after week by workers submitting a form. The "created" column serves as the date/time entry. Every time I try to plug date criteria into the formula I get an error. My attempt at using the suggested countifs() with date criteria looks like the following and did not work:

=IF(COUNTIFS({Toolbox Talk sign in Range 1}, [Primary Column]@row, {Toolbox Talk sign in Range 4}, ">=" & (TODAY() - 7)) > 0, "", "Not Submitted")

Appreciate any suggestions

Georgie · 03/12/25