If statement referencing another sheet - Outcome Employees that have not submitted their time.

TLaGrant

I am trying to capture employees that have not submitted during a week. I have a sheet (Staff that hasn't submitted) with all the employee names and a yes or no column. I would like to ref the master sheet (Standardized Time Sheet) which also has the employee name and the week ending date which is always a Saturday.

=IF({WeekEnding} = <TODAY(+4), {EmployeeName}@row, 0)

TLaGrant

I have also tried

=IF(AND({Standardized Time Sheet Range 1}@row >= TODAY(+4), {Standardized Time Sheet Range 3}@row = "Yes"), "Submitted", "Not Submitted")

Paul Newcome

What is the end goal for collecting this data?

TLaGrant

I want to see who has not submitted so I can create an automation to send out a reminder email to submit their time

Paul Newcome

Are you able to provide some screenshots with sample data?

TLaGrant

Main Time sheet

Sheet: Staff that Hasn't submitted

Basically if there is an entry and it was approved sent to payroll will say yes. If there is no entry or the time has been denied "no" I would like to chow that on a report as people that have not submitted there time

=IF(AND({Standardized Time Sheet Range 1} >= TODAY(+4), {Standardized Time Sheet Range 3} = "TRUE", {Standardized Time Sheet Range 3} = 1), "Submitted", "Not Submitted"). This retuned Invalid Operation

=IF(AND(VLOOKUP({Standardized Time Sheet Range 1}@row, {Standardized Time Sheet Range 1}, 1, FALSE) <= TODAY(+4), VLOOKUP({Standardized Time Sheet Range 3}@row, {Standardized Time Sheet Range 3}, "Yes", FALSE)), "Submitted", "Not Submitted"). This was unparseable

=IF(AND({Standardized Time Sheet Range 1}@row >= TODAY(+4), {Standardized Time Sheet Range 3}@row = "Yes"), "Submitted", "Not Submitted"). This was unparseable