Checkbox Cross Referencing With Time Submission

fergus chan

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!

Paul Newcome

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:

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

fergus chan

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

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