How can I update a checkbox on a sheet based off of criteria from another sheet?

Tana V
Tana V ✭✭✭
edited 08/04/23 inSmartsheet Basics

I'm trying to update a checkbox on a sheet based off of criteria from another sheet. I've seen several examples using the INDEX / MATCH function but haven't had any luck because the situations seem to be a little different than mine.

Here is my situation:

Sheet 1: Time Tracking- updated by each employee each week via a form;

Columns in play: Employee ID, Employee Name, Week Starting (date) = dropdown list with all week of dates for 2023. This sheet only includes the employees that DID enter time for the week.

Sheet 2: Time Tracking - Validation by Week- has an entry forALLemployees and checkboxes for each of the week of dates.

Columns in play: Employee ID, Employee Name, a checkbox column for each weeks entry (7/10, 7/17, 7/24, etc.)

My goal is the have the week of date checkbox (ex; 0710) update IF a record is found in the Time Tracking sheet with a matching Employee ID and Week Of date so I can then filter out what is not checked for a specific week to know the employees that DIDN'T enter their time.

If the Time Tracking sheet (Sheet 1) has a record for Employee 1234567 for Week of 7/10/2023 then update the Validation Sheet (Sheet 2) with a checkmark for the 7/10 column for that employee.

Validation Sheet (2): (WO = week of / checkboxes)

Snag_130d01cb.png

Time Entry Sheet (1): (Week Starting = WO in sheet 2)

Snag_130baa88.png

Thank you in advance!!


Edit: I also have a column in Sheet 1 that joins the EmpID+EmpName+WeekStart in case that can be used. I could create the same thing in Sheet 2 but I don't have the Week Start so doesn't seem to be helpful.

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would use an IF/COUNTIFS combo to count how many rows have the employee ID and the Week Starting. If that count is greater than zero (meaning there was at least one entry), check the box.


    =如果(条件统计({Source Sheet EMP ID}, @cell = [Employee ID]@row, {Source Sheet Week Starting}, @cell = "7/10/2023")> 0, 1)

Answers

Hi DnG,<\/p>

Click on Share button of your Dashboard and fill in emails that you want to give access.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108568,"type":"question","name":"I have a dashboard that I need to export as a PDF","excerpt":"I have set up a dashboard for our meeting agendas with clients. We take meeting notes right in the agenda dashboard. After the calls, I would like to export it to a PDF so that it memorializes our notes from that meeting. The issue is the dashboard does not show EXPORT under FILE. Any suggestions?","snippet":"I have set up a dashboard for our meeting agendas with clients. We take meeting notes right in the agenda dashboard. After the calls, I would like to export it to a PDF so that it…","categoryID":321,"dateInserted":"2023-08-04T22:20:09+00:00","dateUpdated":null,"dateLastComment":"2023-08-04T22:46:47+00:00","insertUserID":78796,"insertUser":{"userID":78796,"name":"RDraper","url":"https:\/\/community.smartsheet.com\/profile\/RDraper","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!b1Dcm4hhQXQ!0_jdP4h1QLQ!VfjUZTtcANv","dateLastActive":"2023-08-04T22:47:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-06T20:31:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":37,"score":null,"hot":3382377416,"url":"https:\/\/community.smartsheet.com\/discussion\/108568\/i-have-a-dashboard-that-i-need-to-export-as-a-pdf","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108568\/i-have-a-dashboard-that-i-need-to-export-as-a-pdf","format":"Rich","lastPost":{"discussionID":108568,"commentID":389064,"name":"Re: I have a dashboard that I need to export as a PDF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389064#Comment_389064","dateInserted":"2023-08-04T22:46:47+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-06T20:31:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T22:48:25+00:00","dateAnswered":"2023-08-04T22:46:47+00:00","acceptedAnswers":[{"commentID":389064,"body":"

\"Printing\" the dashboard will create a pdf.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108540,"type":"question","name":"How can I update a checkbox on a sheet based off of criteria from another sheet?","excerpt":"I'm trying to update a checkbox on a sheet based off of criteria from another sheet. I've seen several examples using the INDEX \/ MATCH function but haven't had any luck because the situations seem to be a little different than mine. Here is my situation: Sheet 1: Time Tracking - updated by each employee each week via a…","snippet":"I'm trying to update a checkbox on a sheet based off of criteria from another sheet. I've seen several examples using the INDEX \/ MATCH function but haven't had any luck because…","categoryID":321,"dateInserted":"2023-08-04T16:05:43+00:00","dateUpdated":"2023-08-04T17:19:39+00:00","dateLastComment":"2023-08-04T22:00:55+00:00","insertUserID":144833,"insertUser":{"userID":144833,"name":"Tana V","url":"https:\/\/community.smartsheet.com\/profile\/Tana%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xIfp8WvfKzM!BqnoQUvuP7M!zhz8Yul5dPv","dateLastActive":"2023-08-04T22:00:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":144833,"lastUserID":144833,"lastUser":{"userID":144833,"name":"Tana V","url":"https:\/\/community.smartsheet.com\/profile\/Tana%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xIfp8WvfKzM!BqnoQUvuP7M!zhz8Yul5dPv","dateLastActive":"2023-08-04T22:00:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":43,"score":null,"hot":3382352798,"url":"https:\/\/community.smartsheet.com\/discussion\/108540\/how-can-i-update-a-checkbox-on-a-sheet-based-off-of-criteria-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108540\/how-can-i-update-a-checkbox-on-a-sheet-based-off-of-criteria-from-another-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108540,"commentID":389058,"name":"Re: How can I update a checkbox on a sheet based off of criteria from another sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389058#Comment_389058","dateInserted":"2023-08-04T22:00:55+00:00","insertUserID":144833,"insertUser":{"userID":144833,"name":"Tana V","url":"https:\/\/community.smartsheet.com\/profile\/Tana%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xIfp8WvfKzM!BqnoQUvuP7M!zhz8Yul5dPv","dateLastActive":"2023-08-04T22:00:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/FX55FDBTGBGR\/snag-130d01cb.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Snag_130d01cb.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T22:00:25+00:00","dateAnswered":"2023-08-04T18:46:52+00:00","acceptedAnswers":[{"commentID":389011,"body":"

You would use an IF\/COUNTIFS combo to count how many rows have the employee ID and the Week Starting. If that count is greater than zero (meaning there was at least one entry), check the box.<\/p>


<\/p>

=IF(COUNTIFS({Source Sheet EMP ID}, @cell = [Employee ID]@row, {Source Sheet Week Starting}, @cell = \"7\/10\/2023\")> 0, 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4916,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics