Checkbox Cross Referencing With Time Submission

Options
fergus chan
fergus chan ✭✭
edited 09/08/23 in Formulas and Functions

Hi all,

I have two sheets. Sheet A has a full list of employees with a blank checkbox column for survey completion, while Sheet B is the survey sheet that has a time-submitted column for the survey(+columns for department and employee names).

What is an easy formula to use so that when an employee fills out a survey, it generates the time completed on Sheet B, which then cross-references Sheet A, and automatically checks off their name on sheet A?

Thank you!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I developed a method for capturing a date/time stamp when specific actions are completed. It does involve a third sheet in this case.


    What you will need to do is set up another sheet that replicates Sheet B (save as new and remove existing data and formulas). We will call this Sheet C.

    Then you would go back to Sheet B and set up a copy row automation to copy rows over to Sheet C when the survey has been completed.

    Finally on Sheet A we would use either an INDEX/MATCH or INDEX/COLLECT to pull that date/time stamp over from Sheet C.


    Are you able to provide screenshots of your current sheets so we can determine the best formula for pulling from C to A?


    Below is a link to a thread with more details on capturing the date/time stamp:


  • fergus chan
    Options

    Hi Paul! That's an interesting idea. I've set up Sheet B and Sheet C per your suggestion. Below is what I have for Sheet A and Sheet C (the copied one that you suggested).



    Ideally, "Submission Status" would be the column when someone fills out the form, it checks off their names automatically. Right now, my formula counts the amount of responses from Sheet B, so I tested to have mickey mouse fill out the survey. But it doesn't sync right now.

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you just trying to check the box, or are you trying to pull in the time stamp?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!