How can I create a formula to list names that haven't been added (cross sheet)

daonnen

I am trying to have a summary list all of the names that are on one source sheet but are not populated in destination sheet. I am creating a time entry process for my company and would like a summary section to show who has not yet submitted time based on the list from another sheet. So if Sanchez has not entered their time on the form their name will be listed but since Rodriguez has entered their time their name is not listed.

Source Sheet:

Destination Sheet:

brianschmidt

Without necessarily knowing more details, I would probably create a helper checkbox column that checks whether that employee has time entered or not. This would likely go in your reference sheet that houses all of your names. The column formula would look something like this:

=IF(COUNTIFS({Employee ID}, [Employee ID]@row, {Employee ID}, NOT(ISBLANK(@cell))) > 0, 1, 0)

The "{Employee ID}" reference would be a range referencing an Employee ID (or whatever the unique identifier is..name, etc.) column in the time entry sheet.

Then, you can create a row report with a filter that only shows rows where the checkbox is not checked…thus showing you which employees have and haven't entered time.

Hope this helps!:)

daonnen

This is not quite what I am looking for. I am trying to create a formula so that it lists the last name of the employees that are contained in the source sheet only if they are not appearing in the destination sheet. This is the formula I have currently been playing with

=IF(NOT(CONTAINS({2R Payroll List Range 1}, [Employee Last Name]:[Employee Last Name])), JOIN({2R Payroll List Range 1}, ", "))

But I get Invalid Data Type Error

https://community.smartsheet.com/discussion/comment/427699#Comment_427699

brianschmidt

Yeah, I think the issue here is that the formula needs to be able to test each input in the reference sheet to see whether they are present in your time-keeping sheet. As your formula is written, it asks to search for the entire cross sheet column in the last Employee Last Name column. The closest I've been able to get to get this to work is the following formula:

=JOIN(DISTINCT(COLLECT([Primary1]:[Employee List], [Primary1]:[Employee List], NOT(ISBLANK(@cell)))), ", ")

However, this assumes that (in your case) the reference list of employees and entered time list of employees is in the same sheet.

If you try to pull in the list of employees into one cell, it gets hard to look for distinct values, since it will treat the inputs in that cell as a singular input (all last names mashed together).

I honestly think that it may be easier to have a reference sheet associated with each time-keeping sheet with the checkbox we talked about in my first response. Then, use a JOIN(COLLECT formula in your time-keeping sheet that would reference the reference sheet to pull in all the last names that are not checked. You could then pull them into the summary cell you are looking to create as opposed to into a report as initially proposed.