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.

Formula renumbers when row added - I don't want it to.
I have a formula that populates the Asset # =IF(COUNT(ANCESTORS([Deliverables and Tasks]@row)) = 2, (Product@row) + 10 + COUNT(DISTINCT([Asset #]$1:[Asset #]6)), PARENT()) This generates MA100, MA101, etc. where each Ancestor = 2 (basically) This works fine EXCEPT when rows are added in between tasks. Then it renumbers the…
Make a cell red if blank
This seems like it should be a simple task, but I am having challenges getting it to work. I have a need to have a cell turn red if there is no data in it. Once it is populated, the red goes away. I'm sure there is a simple solution but cannot seem to figure it out. Thanks in advance for the help.
Set and Retain Date Field Value
Hello all!! I have a dropdown, single select field named STATUS with values of SUBMITTED and RESOLVED. I also have a date field named RESOLVED DATE. I am trying to automagically set the RESOLVED DATE to the date the user selects the RESOLVED status and then retain that date everyday unless the status is changed to…
Help with date formula's
I am creating a tracker that will keep track of vacation days, and the number of days an employee gets each year depends on how long they have been an employee. The days are earned each year on their anniversary. I have almost everything working with the exception of 1 thing. Let's say I have an employee that started…
Why does a new submitted form appear to row 52?
Hi For some reason all the new submitted forms appear to row 52. Is there a setting I can change so the all the new submitted forms appear to the top (row1)? Info: -Form type is modern
Problems report filter and logic formula not recognizing today and yesterday in date column
I'm trying to get my report to filter based on rows where the date column "Last Response Received" is not today or yesterday: Last Response Received is a date column that has another formula: MAX(COLLECT(External sheet reference)...) which returns the most recent date (from a user's input of form data into a separate…
Conditional formatting on a cell that has multiple date input from a form
Help needed and much appreciated!!! I have a built a form to track new contracts, I input the data manually and one of the options are upgrades. The upgrade field in the form is a drop-down multi select option and I would like a different column to automatically calculate the price based on the options selected. Is is it…
We are trying to figure out how to make a dashboard have selectable date ranges
Please Help
Calculations
Hello Smartsheet Users, I have a project I am working on and I need to my calculation to look at two different columns. So here it is I have a column with Employee ID's and a Date Column. I need to a column to show a flag if the employee ID shows up more than once within 30 days. The calculation will look back at the…
COUNTIFS PARENT cross reference
In my summary sheet, I need to cross reference the PARENT rows in my data sheet (status) to count the number of open non-conformities (with less than 100% complete). Basically, the counting in summary sheet needs to take into consideration two conditions in the status sheet: Be a PARENT row, and % complete <1 I have tried…

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