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.

Conditional formatting for past dates
Hello, I need some assistance in creating a conditional format where "Active" will turn red in the Status column if the Last Reviewed date is at least five days in the past. This should only happen for the cells that are currently gold. None of the date inspection choices in conditional formatting seem to fit this…
Is there a way to use the current user filter just on some people and not everyone?
Is there a way to use the current user filter so that people can only see the rows they have been assigned to, but other people can see the whole sheet? I am working on a confidential project where there are 4 people in charge and need to see all tasks. But they want to assign tasks to other people without them seeing the…
Is there a way to request sign-off/ approval for only certain rows in a sheet?
I am trying to create an option where all of the rows under PressRoom Action Items require a sign off or approval. I tried to do automation for this but I couldn't find a way to make it just for those particular rows. Is there another way? I also thought about forms but it didn't seem to make sense for what I am doing.…
Fixed project end date with predecessors
I have a plan with durations and predecessors. I was given a hard end for the project to finish. Is there a way for smartsheet to calculate the start and end dates for the tasks leading up to the hard finish date?
Formula Help
Hello, I feel like I am close with the current formula I am using in a build out. Could use some help if anyone has suggestions. I have a section where AutoFail is possible and then a Section that is Pass/Fail based on %'s. If they Pass the Pass/Fail Portion but Auto Fail the other portion the entire total score is Auto…
Label Status Dot Help
Hey all! I am trying to add the color symbol dots to align with status and I've tried so many formulas, I cannot for the life of me figure this out. Nothing crazy in this sheet, just want to align the status to the color of the dot. If Status = Complete —> Green If Status = In Discovery, In Process, In Review —> Yellow If…
Automated way to move "Comments" (Conversations in Smartsheet) from column to Conversations
Hi, I have a team maintaining an Action Tracker. All are relatively new to Smartsheet from Excel and most are at a higher pay grade than me. As such, they asked for a "Comments" column in the Action Item grid to track DAILY actions and updates. As you can imagine the row sizes are quickly becoming unmanageable. Is there…
Assigning Score to Multi-Select Dropdown Menu Options
Hi, I need assistance with assigning a score for outputs using a multi-select dropdown menu. Essentially, I want to be able to assign a numerical value when someone selects a choice from a multi-select dropdown, however, I am only able to assign a score for one answer. When multiple options are selected, the IF formula I'm…
Can Unlicensed Users Edit Reports
According to the [docs](https://help.smartsheet.com/learning-track/free-users#:~:text=Free%20users%20have%20the%20ability,own%20items%20without%20a%20license.) "Free users have the ability to view and/or edit the items they have been shared to". I would assume items would include reports, but, as unlicensed user, I can…
How to rank highest to lowest
I would like to rank a range where the #1 rank is the lowest number in a range. The formula I am using ranks the highest number as #1 =RANKEQ([Revenue Per Year]1, [Revenue Per Year]:[Revenue Per Year], 1)

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