Update a cell value based on previous entry
我有一个筛选log for people going in and out of an area — I am wondering if it is possible to look at their previous row for direction (in/out), and default to out if no previous data has been entered for them.
I am currently using an identifier column for NAME + COMPANY via index match to essentially create a profile in a separate sheet and datamesh to update cell values, but I’m missing the appropriate logic and I’m wondering if there is a more simple way to achieve this?
雅各Stey
Patriot Group International
Answers
-
Jason Albrecht ✭✭✭✭✭Hello@SteyJ
May I confirm I understand the question properly please?
Do you mean to achieve something like this?
This formula is looking for the result of the match to be a value above zero. If all three exist the result is an "in" status, otherwise the default status is "out"
What I'm not sure about is how the data is entered into the screening log, whether the data is entered at the top of the sheet or bottom and if/whether any rows of data are deleted (daily, weekly, monthly), which would affect the formula.
Hope this helps and that you have a great day,
Jason
Categories
<\/p>
Try this instead:<\/p>
=INDEX([AT-Meta]:[AT-Meta], 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":[]},{"discussionID":109199,"type":"question","name":"How long should tech support take to respond?","excerpt":"On August 14 I sent tech support a critical question. I have a licensed admin on a sheet that is not able to make any changes. I still have not received any response other than the usual quickie we got your e-mail. If someone in tech support is watching my case number is. 06399972","snippet":"On August 14 I sent tech support a critical question. I have a licensed admin on a sheet that is not able to make any changes. I still have not received any response other than…","categoryID":321,"dateInserted":"2023-08-21T15:19:29+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T15:19:29+00:00","insertUserID":1774,"insertUser":{"userID":1774,"name":"Joseph Farahat","url":"https:\/\/community.smartsheet.com\/profile\/Joseph%20Farahat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T16:15:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-21T18:35:02+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":true,"countComments":5,"countViews":41,"score":null,"hot":3385265338,"url":"https:\/\/community.smartsheet.com\/discussion\/109199\/how-long-should-tech-support-take-to-respond","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109199\/how-long-should-tech-support-take-to-respond","format":"Rich","lastPost":{"discussionID":109199,"commentID":391636,"name":"Re: How long should tech support take to respond?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391636#Comment_391636","dateInserted":"2023-08-21T15:19:29+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-21T18:35:02+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-21T16:29:36+00:00","dateAnswered":"2023-08-21T15:54:56+00:00","acceptedAnswers":[{"commentID":391614,"body":"
Fixed.<\/p>
I reread your response and found the culprit.<\/p>
“Report with specific permissions but not the underlying source sheet?”<\/p>
Thank you for jumping in. I guess you should close that case number at this point.<\/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":109045,"type":"question","name":"Milestones showing at start of next day vs. end of day","excerpt":"I noticed a change in the milestone logic today, and I'm wondering if there's a way to revert back to how it was before or if there is something I'm missing. Before today, a milestone (a task with 0 duration) would show that milestone occurs at the END of the day of the predecessor task. But now, it has the milestone task…","snippet":"I noticed a change in the milestone logic today, and I'm wondering if there's a way to revert back to how it was before or if there is something I'm missing. Before today, a…","categoryID":321,"dateInserted":"2023-08-16T17:00:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T14:14:43+00:00","insertUserID":165212,"insertUser":{"userID":165212,"name":"Vivian_Vivian","url":"https:\/\/community.smartsheet.com\/profile\/Vivian_Vivian","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:53:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":137695,"lastUser":{"userID":137695,"name":"Tim Starkey","url":"https:\/\/community.smartsheet.com\/profile\/Tim%20Starkey","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uolS96T7mzo!84NRtwSFw3o!n5WODiM4TKP","dateLastActive":"2023-08-21T14:30:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":140,"score":null,"hot":3384838527,"url":"https:\/\/community.smartsheet.com\/discussion\/109045\/milestones-showing-at-start-of-next-day-vs-end-of-day","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109045\/milestones-showing-at-start-of-next-day-vs-end-of-day","format":"Rich","tagIDs":[319,439],"lastPost":{"discussionID":109045,"commentID":391608,"name":"Re: Milestones showing at start of next day vs. end of day","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391608#Comment_391608","dateInserted":"2023-08-21T14:14:43+00:00","insertUserID":137695,"insertUser":{"userID":137695,"name":"Tim Starkey","url":"https:\/\/community.smartsheet.com\/profile\/Tim%20Starkey","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uolS96T7mzo!84NRtwSFw3o!n5WODiM4TKP","dateLastActive":"2023-08-21T14:30:05+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\/NYP1WSBPJHF2\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T08:34:24+00:00","dateAnswered":"2023-08-21T08:34:18+00:00","acceptedAnswers":[{"commentID":391564,"body":"
Hi all (@Vivian_Vivian<\/a>, @Jamila<\/a>, @Tim Starkey<\/a>, @Madeleine Kudritzki<\/a>)<\/p> Just a note here to say that based on the numerous reports to Support of customer impact, this change has been rolled back to the previous Milestone behaviour: tasks that have 0 duration and start at the end of the day will stay<\/strong> at the end of the day instead of moving over to the next day. <\/p> Thank you,<\/p> Genevieve<\/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":319,"urlcode":"functionality","name":"functionality"},{"tagID":439,"urlcode":"gantt-view","name":"Gantt View"}]}],"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":4962,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
