Send report to manager of those who haven't completed form submission request.

ben_kaiwaka

Hi,

I have created a form that is sent daily to all employee's at 4pm where they have to provide a review of the day.

At 6pm, a report is then sent to the respective managers showing what their direct reports have submitted.

However, I somehow need the report that is sent to the manager to show those who haven't submitted their forms.

Paul Newcome

=COUNTIFS({Form Sheet DAte Column}, @cell = TODAY(), {Form Sheet Name Column}, @cell = [Name COlumn]@row)

Paul Newcome

You would need to have a comprehensive list of all employees. From there you would use a COUNTIFS on each row to count how many times that employee's name shows up on the form submission sheet for TODAY().

If that COUNTIFS(.....) = 0, then that employee has not filled it out for today.

ben_kaiwaka

Thanks Paul. What would the complete formula be??

Paul Newcome

You would use a COUNTIFS to look for the name and the date of today.

=COUNTIFS({1st Criteria Range}, 1st Criteria, {2nd Criteria Range}, 2nd criteria)

ben_kaiwaka

Thanks but I really can't work out how to formulate this.

Column 1 is name - "Cindy Balfour"

Column 2 is date - "26/01/2023"

Paul Newcome

=COUNTIFS({Form Sheet DAte Column}, @cell = TODAY(), {Form Sheet Name Column}, @cell = [Name COlumn]@row)

ben_kaiwaka

Thanks, worked perfectly!!

Paul Newcome

Happy to help. 👍️