Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.

Product questions?
Ask 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.

Highlight parent if child task is in the past
Hi I have a sheet that has a column called Task name it has a main task with 4 subtasks (child) below it. The sheet has a Task name, Status and due date column. If the 1st sub task due date is in the past and not marked complete how do I have it highlight the parent of this task Red. If the 1st is complete it is no color…
How to assign points to answers selected thru drop down on form
Hello, I created a form to be used during a training exam. Each cell has a drop with 3 options, Proficient, Needs Improvement, Not covered. Is it possible to assign each option a point value ( 3 - proficient, 2 Needs Improvement, 1 not covered) and have a total calculated once completed? Thank you
Need help with a cross-sheet reference formula.
I am trying to create a cross-sheet formula so I can count the number of projects for a specific PM where the show start data is in the month of January and year is 2024. The source sheet contains show start dates that are in 2025 so I want to be able to avoid counting them by mistake. I'm basically trying to count the…
How can I reference a sheet summary
I am building a parent sheet to create reports from a number of child sheets. I would like to reference the sheet summary of the child sheets in the parent sheet to use in calculations.
How to Calculate Age from a Specific Date & Average Results
I am trying to average the age of multiple events as of specific dates (end of each month). Currently I have "Age" calculated from "Today()" in a column in another sheet. I would like to instead calculate the Age from "Date of Notification" to "End Date", and would like to avoid using a helper column to do so. I do not…
Pre/Post Survey matched by Employee ID
I have a survey with field "Type" (Pre-Survey or Post-Survey). I have another field with a Unique id (employee ID). On the Pre-Survey we ask a question called "Segment" (which areas they work in), however we do not ask that question in the post-survey. I have the segment field allowing multiple selection then I use hidden…
Is there a way to have form pop up when a box is checked? Or have a form be an option for an update
I am trying to create a project where when someone checks a box, it requires them to fill out a form that would email out to a group to let them know that they need help. Is this possible or are there better alternatives? My idea here is there if a team is working on a project and they are held up on something and that is…
Combine IF formulas in one cell
Hello, I have the following table (below) with a column ''amount of per diem used'' and a column ''number of days abroad''. The total amount of per diem will depend on whether the 40% rate applies, or the 70% rate or the full amount. So, I would like to use a multiple formula in one cell that says: if the box 40% per diem…
Calaulate activity health based on % complete and Planned Finish date
In my sheet I have a column "% Complete" and "Planned FInish". Is it possible to create a formula which can highlight the row color in red, blue or green or yellow if % complete is less then 100% and planned finish is over a certain date (example 10 Dec 2024). I am new at smartsheet, need help from our community.
Can the auto-numbering function be set up to utilize new numbering prefix at start of each year?
I created a sheet that uses auto-numbering to assign a number to all submitted forms. I want the numbering to utilize a prefix denoting the year and start over at 0 each year. For example the numbering of the submissions in year 2021 will start with 2021-01 continuing with 2021-02, 2021-03, etc. When the year 2022 is…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags