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

Options

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)

Answers

  • TLaGrant
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    What is the end goal for collecting this data?

  • TLaGrant
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots with sample data?

  • TLaGrant
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!