Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook templateto view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
Product questions?
Sign in andask it here!The community's got your back.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Discussion List

3; IF(TODAY() - Due4 > -7; \"Red\"; IF(TODAY() - Due4 > -14; \"Yellow\"; IF(TODAY() - Due4 < 6; \"Green\")))) Now i want to add another formula to column(Drop…","categoryID":322,"dateInserted":"2018-06-08T00:41:26+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-12T13:59:56+00:00","insertUserID":50641,"insertUser":{"userID":50641,"name":"Tauri","url":"https:\/\/community.smartsheet.com\/profile\/Tauri","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-18T10:42:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":50641,"lastUser":{"userID":50641,"name":"Tauri","url":"https:\/\/community.smartsheet.com\/profile\/Tauri","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-18T10:42:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":80,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/26636\/symbols-not-changing-based-on-the-value-in-another-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26636\/symbols-not-changing-based-on-the-value-in-another-cell","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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}]},{"discussionID":26731,"type":"discussion","name":"Tracking boxes checked per user per day?","excerpt":"Is there any formula I could use that would track the number of boxes checked by individual users for a specific day? I know I can generate this data in a report, but I'd like to create a formula so I could build around it. ","categoryID":322,"dateInserted":"2018-06-11T12:30:59+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-12T01:00:55+00:00","insertUserID":50886,"insertUser":{"userID":50886,"name":"Mason Granato","url":"https:\/\/community.smartsheet.com\/profile\/Mason%20Granato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-11T12:26:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":23666,"lastUser":{"userID":23666,"name":"Chris McKay","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20McKay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-09\/d5\/d2\/nd5d2693be28ccbd24f978b00c5d1db1d.JPG","dateLastActive":"2018-08-14T22:59:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":57,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/26731\/tracking-boxes-checked-per-user-per-day","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26731\/tracking-boxes-checked-per-user-per-day","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":271,"urlcode":"services","name":"Services"},{"tagID":281,"urlcode":"support","name":"Support"}],"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}]},{"discussionID":26706,"type":"discussion","name":"Either \/ Or but not both","excerpt":"Hi Could someone help please? I'd like to set a formula in a sheet with say 3 columns. It needs to sum\/count depending on the answer of the first two columns but not them both. If either of the columms say \"Yes\" then that is good enough to be counted in the 3rd column. For example: Column A: Yes Column B: Yes Total: 1…","categoryID":322,"dateInserted":"2018-06-10T17:38:01+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-11T19:21:56+00:00","insertUserID":5835,"insertUser":{"userID":5835,"name":"Nick Randall","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Randall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-10T17:31:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":23666,"lastUser":{"userID":23666,"name":"Chris McKay","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20McKay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-09\/d5\/d2\/nd5d2693be28ccbd24f978b00c5d1db1d.JPG","dateLastActive":"2018-08-14T22:59:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":186,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/26706\/either-or-but-not-both","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26706\/either-or-but-not-both","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":492,"urlcode":"media-entertainment","name":"Media & Entertainment"}],"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}]},{"discussionID":26611,"type":"discussion","name":"Conditional Formatting","excerpt":"Is there a way to add a conditional format to a cell that has a drop down RYG...like project health? If a task date is past due, I want the health cell to change from green to yellow automatically. Thanks for any help!","categoryID":322,"dateInserted":"2018-06-07T17:23:23+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-11T17:27:22+00:00","insertUserID":42336,"insertUser":{"userID":42336,"name":"Linda McGee","url":"https:\/\/community.smartsheet.com\/profile\/Linda%20McGee","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-05\/99\/f2\/n99f28036fa241141ea5704e47828bc2a.JPG","dateLastActive":"2023-04-19T16:05:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":42336,"lastUser":{"userID":42336,"name":"Linda McGee","url":"https:\/\/community.smartsheet.com\/profile\/Linda%20McGee","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-05\/99\/f2\/n99f28036fa241141ea5704e47828bc2a.JPG","dateLastActive":"2023-04-19T16:05:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":232,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/26611\/conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26611\/conditional-formatting","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}],"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}]},{"discussionID":26561,"type":"discussion","name":"Formula Predecessor Help 2...","excerpt":"I could use some help. I've implemented J. Craig Williams' predecessor solution allowing notifications based on the predecessor being complete for a given task: 1. Create a column to get your Row# [RowID] In the first row, put a 1. The second row, use the formula =RowID1 +1 and copy that to all the other rows. 2. Create a…","categoryID":322,"dateInserted":"2018-06-07T11:11:10+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-11T14:10:50+00:00","insertUserID":47276,"insertUser":{"userID":47276,"name":"Robert Meisch","url":"https:\/\/community.smartsheet.com\/profile\/Robert%20Meisch","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mTPCXjqFsv4!BJdBwabKLbQ!HkmolwBfjJd","dateLastActive":"2022-11-22T17:39:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":195,"lastUser":{"userID":195,"name":"Shaine Greenwood","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Shaine%20Greenwood","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/198\/nBSBY6VR571R7.jpg","dateLastActive":"2021-08-30T16:03:52+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":98,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/26561\/formula-predecessor-help-2","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26561\/formula-predecessor-help-2","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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}]},{"discussionID":26676,"type":"discussion","name":"Harvey Balls \/ Lookup or Max ?","excerpt":"Dear all, I am trying to find a smart way to use formulas to make a large data table more accessible. The situation is as follows: I have a number of companies that are represented by one line in smartsheet. This line included a unique ID as well as names, etc. For each companies I can have 1 or more lines - children -…","categoryID":322,"dateInserted":"2018-06-08T12:46:33+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-11T09:25:19+00:00","insertUserID":50706,"insertUser":{"userID":50706,"name":"madamaschke","url":"https:\/\/community.smartsheet.com\/profile\/madamaschke","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-13T12:54:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":16866,"lastUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T15:21:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":75,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/26676\/harvey-balls-lookup-or-max","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26676\/harvey-balls-lookup-or-max","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"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}]},{"discussionID":26586,"type":"discussion","name":"Counting formula to see how many status each person has","excerpt":"So I want to see how many Not Started, In-Progress or Completed each person has.","categoryID":322,"dateInserted":"2018-06-07T14:30:55+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-08T11:50:24+00:00","insertUserID":27746,"insertUser":{"userID":27746,"name":"mark.gipson27746","url":"https:\/\/community.smartsheet.com\/profile\/mark.gipson27746","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-10-01T16:57:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-10-25T23:07:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":234,"score":null,"hot":8,"url":"https:\/\/community.smartsheet.com\/discussion\/26586\/counting-formula-to-see-how-many-status-each-person-has","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26586\/counting-formula-to-see-how-many-status-each-person-has","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":207,"urlcode":"sales","name":"Sales"},{"tagID":234,"urlcode":"health-care","name":"Healthcare"},{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":26511,"type":"discussion","name":"Formula","excerpt":"Hi, Hopefully I'll explain my predicament correctly! Ive set up a sheet for the commercial department to track orders. I need a formula to track the value of orders quoted in a certain month. The column I need the date range to come from is called Date Out The column I need the descriptor to come from is called Status, and…","categoryID":322,"dateInserted":"2018-06-06T08:47:34+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-08T10:24:00+00:00","insertUserID":50311,"insertUser":{"userID":50311,"name":"padw","url":"https:\/\/community.smartsheet.com\/profile\/padw","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-06T08:38:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":50311,"lastUser":{"userID":50311,"name":"padw","url":"https:\/\/community.smartsheet.com\/profile\/padw","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-06T08:38:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":55,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/26511\/formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26511\/formula","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":218,"urlcode":"construction","name":"Construction"},{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":26616,"type":"discussion","name":"Auto-populating a sheet with filtered data from another","excerpt":"All, I'm hoping someone can suggest a solution. I am trying to reduce the amount of time spent re-entering data about upcoming bids from one spreadsheet to another. My company has a master list of bids over the fiscal year and every week my boss produces a separate sheet of just the upcoming bids that he can then use…","categoryID":322,"dateInserted":"2018-06-07T17:58:23+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-06-08T02:36:22+00:00","insertUserID":50586,"insertUser":{"userID":50586,"name":"Joshua Fargason","url":"https:\/\/community.smartsheet.com\/profile\/Joshua%20Fargason","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-06\/0b\/66\/n0b66755f80fd30b6d29d5dcb88c63397.jpg","dateLastActive":"2020-02-12T16:00:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-31T16:17:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":256,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/26616\/auto-populating-a-sheet-with-filtered-data-from-another","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26616\/auto-populating-a-sheet-with-filtered-data-from-another","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":218,"urlcode":"construction","name":"Construction"},{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"},{"tagID":507,"urlcode":"enhancement-request","name":"Enhancement Request"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi @sstubbs<\/a> <\/p>

You can get the time part of the \"Created Date\" value in 24-hour format with the following formula;<\/p>

=TIME(RIGHT(Created@row, 8), 1)<\/p>

Then you can get the \"Hour\" part by the following formula;<\/p>

=VALUE(LEFT([24 Hour Format]@row, 2))<\/p>

Then, using a metric sheet counting data grouping by hour, you can create a chart in a dashboard.😁<\/span><\/p>

\n \n https:\/\/app.smartsheet.com\/b\/publish?EQBCT=9561450106084517a0027e6fd2b37b09\n <\/a>\n<\/div>
\n
\n \n \"Count<\/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":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"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":292,"urlcode":"dashboards","name":"Dashboards"},{"tagID":499,"urlcode":"time-calculation","name":"Time Calculation"}]},{"discussionID":112354,"type":"question","name":"IF, AND\/OR, NOT formula with multiple conditions for a symbol column","excerpt":"Hello, I have been using a symbol column to monitor updates for a master sheet of all of my departments projects: =IF([Exec Summary Update]@row < TODAY(-14), \"Red\", IF([Exec Summary Update]@row < TODAY(-8), \"Yellow\", \"Green\")) However, I need to update this formula to include criteria that states NOT if the Phase@row is…","snippet":"Hello, I have been using a symbol column to monitor updates for a master sheet of all of my departments projects: =IF([Exec Summary Update]@row < TODAY(-14), \"Red\", IF([Exec…","categoryID":322,"dateInserted":"2023-10-30T21:58:58+00:00","dateUpdated":null,"dateLastComment":"2023-10-31T14:00:38+00:00","insertUserID":144966,"insertUser":{"userID":144966,"name":"kelceyg","title":"Associate Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/kelceyg","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QOWM4DMFF87J\/n3NJCD8AR6ZO1.jpg","dateLastActive":"2023-10-31T14:00:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":144966,"lastUser":{"userID":144966,"name":"kelceyg","title":"Associate Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/kelceyg","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QOWM4DMFF87J\/n3NJCD8AR6ZO1.jpg","dateLastActive":"2023-10-31T14:00:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":30,"score":null,"hot":3397465176,"url":"https:\/\/community.smartsheet.com\/discussion\/112354\/if-and-or-not-formula-with-multiple-conditions-for-a-symbol-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112354\/if-and-or-not-formula-with-multiple-conditions-for-a-symbol-column","format":"Rich","tagIDs":[219,254,440],"lastPost":{"discussionID":112354,"commentID":402376,"name":"Re: IF, AND\/OR, NOT formula with multiple conditions for a symbol column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402376#Comment_402376","dateInserted":"2023-10-31T14:00:38+00:00","insertUserID":144966,"insertUser":{"userID":144966,"name":"kelceyg","title":"Associate Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/kelceyg","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QOWM4DMFF87J\/n3NJCD8AR6ZO1.jpg","dateLastActive":"2023-10-31T14:00:50+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-31T14:00:41+00:00","dateAnswered":"2023-10-30T22:44:22+00:00","acceptedAnswers":[{"commentID":402313,"body":"

I've split the answer into 3 steps to make it clear to anyone following along (but I don't think you need anything except the last part).<\/p>

1.<\/p>

This formula will put nothing into your symbol column if Phase is \"Closed\"<\/p>

=IF(Phase@row = \"Closed\", \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", IF([Exec Summary Update]@row < TODAY(-14), \"Red\", IF([Exec Summary Update]@row < TODAY(-8), \"Yellow\", \"Green\")))<\/p>

Here it is in context<\/p>

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

2.<\/p>

This formula will put nothing into your symbol column if Phase is blank<\/p>

=IF(ISBLANK(Phase@row), \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", IF([Exec Summary Update]@row < TODAY(-14), \"Red\", IF([Exec Summary Update]@row < TODAY(-8), \"Yellow\", \"Green\")))<\/p>

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

3.<\/p>

To combine both of these logics use<\/p>

=IF(OR(ISBLANK(Phase@row), Phase@row = \"Closed\"), \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", IF([Exec Summary Update]@row < TODAY(-14), \"Red\", IF([Exec Summary Update]@row < TODAY(-8), \"Yellow\", \"Green\")))<\/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":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]},{"discussionID":112339,"type":"question","name":"Struggling with Formula. Not sure if using correct one.","excerpt":"I have 2 sheets. We'll call Sheet A \"Roster\" and Sheet B \"Grading Sheet\" Sheet A has columns for \"Employee Number\" and \"Email Address\" What I am trying to do is on Sheet B, have a column in which you can enter the employee number, it reference Sheet A and then it will automatically enter the email address associated with…","snippet":"I have 2 sheets. We'll call Sheet A \"Roster\" and Sheet B \"Grading Sheet\" Sheet A has columns for \"Employee Number\" and \"Email Address\" What I am trying to do is on Sheet B, have a…","categoryID":322,"dateInserted":"2023-10-30T18:08:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-30T21:03:37+00:00","insertUserID":162178,"insertUser":{"userID":162178,"name":"Jason F","url":"https:\/\/community.smartsheet.com\/profile\/Jason%20F","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-30T20:38:39+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":146304,"lastUser":{"userID":146304,"name":"KPH","url":"https:\/\/community.smartsheet.com\/profile\/KPH","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BILygZsiKro!BhawQTdaN4g!kpsDfoN5YKE","dateLastActive":"2023-10-31T16:34:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":57,"score":null,"hot":3397394497,"url":"https:\/\/community.smartsheet.com\/discussion\/112339\/struggling-with-formula-not-sure-if-using-correct-one","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112339\/struggling-with-formula-not-sure-if-using-correct-one","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112339,"commentID":402289,"name":"Re: Struggling with Formula. Not sure if using correct one.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402289#Comment_402289","dateInserted":"2023-10-30T21:03:37+00:00","insertUserID":146304,"insertUser":{"userID":146304,"name":"KPH","url":"https:\/\/community.smartsheet.com\/profile\/KPH","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BILygZsiKro!BhawQTdaN4g!kpsDfoN5YKE","dateLastActive":"2023-10-31T16:34:03+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-30T20:02:18+00:00","dateAnswered":"2023-10-30T19:33:31+00:00","acceptedAnswers":[{"commentID":402275,"body":"

That formula looks good. It needs to be in the email cell - where you want the email to appear. The yellow ones here:<\/p>

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

Can you try the cross reference again? Double click on the formula and put your cursor onto the reference like this:<\/p>

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

Then click \"Edit Reference\" on the pop up.<\/p>

Make sure you have highlighted the two columns you need, that the email address is in the second one, and your \"Sheet reference name\" looks exactly <\/strong>like this <\/p>

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

🤞<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":["discussion","question"]},"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=322&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":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Dashboard Element, Count By Created, Grouped by Time (Hour)

    我'm able to quickly accomplish this in Excel, but have no idea how to achieve the same in Smartsheet. We have a call log where a new Created row is automatically generated on submission. I'd like to create a graph that totals all calls by time, specifically the hour of the day. In Excel I create a pivot on the Created…

  • 我F, AND/OR, NOT formula with multiple conditions for a symbol column

    Hello, I have been using a symbol column to monitor updates for a master sheet of all of my departments projects: =IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")) However, I need to update this formula to include criteria that states NOT if the Phase@row is…

  • Struggling with Formula. Not sure if using correct one.

    我have 2 sheets. We'll call Sheet A "Roster" and Sheet B "Grading Sheet" Sheet A has columns for "Employee Number" and "Email Address" What I am trying to do is on Sheet B, have a column in which you can enter the employee number, it reference Sheet A and then it will automatically enter the email address associated with…