function to show current on-call employee from schedule on separate dashboard

I have our team's on call schedule on smartsheet, and i want to display the individual's name for who is on call this week on our team's smartsheet huddle board.

I am assuming I am going to use the IF and VLOOKUP functions (possibly the TODAY function), but I don't know the correct syntax to use!

Goal: cell: row 1, employee column, is linked to huddle board metric (cell already linked, I just need the correct functions to display the name)

Data: I want to display the employee name (the [employee] or [notification] column) in row 1, Employee column, if today's date is between the [on-call start date] and [on-call end date].

Thanks!



Best Answer

  • Paul H
    Paul H Community Champion
    Answer ✓

    Try this

    =IFERROR(INDEX(COLLECT(Notification:Notification, [On-Call Start Date]:[On-Call Start Date], <=TODAY(), [On-Call End Date]:[On-Call End Date], >=TODAY()), 1), "No Match")

    Fix the spelling mistake in you notification column to work 😉

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!