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.

How can I convert date to text?
Hi, I'm setting up data shuttle where unique identifier can be in multiple dates and would need to make sure all data in multiple days flow. In excel, I have a helper column as =ID&"_"&TEXT(Date, "yyyymmdd") to set as an unique identifier. From the Smartsheet that has a date of 7/23/24, how can I convert this into…
Calculation Sheet formula needed
I have an existing Calculation sheet that pulls "linked" information from pivots. I want to simplify and replace with formulas instead from a couple different sheets. (1st Sheet Name: "Migration Grid 03.29.23") Columns In Reference - Key below: "Stars Team" = "MS", IV, etc. "Ident $" = dollars "IMP $" = dollars "Valid $" =…
Index Match with 3 criteria
I want to use the indexMatch formula to get data in the [Column 1], the columns that have to match in the other sheet ([Sheet 2]) are [Project Number], [# Division] and [# Bundle]. I have been trying using the AI formula generator and it gave me this formula: =INDEX({Range Sheet2}, MATCH([Project Number]@row & [#…
Join+Collect by rows
Hello, I am trying to create a smartsheet formula to aggregate summery information from an amendment sheet onto a master sheet. However, when I attempted to use join() +collect() my cell returned all date finalized collected then all #s collected and then all types collected. I want to return data in ROWS not COLUMNS. In…
Use if(contains) based off of items that are submitted today
I want to used if contains to only search on rows submitted today. This formula doesn't work. (Invalid Operation) =IF({Daily Swing Stage Submission Range 1} = TODAY(), IF(CONTAINS([Swing Stage ID]@row, {Swing Stage Submitted}) = "TRUE", "Submitted", "Not Submitted")) But this formula does, but looks at all entries in the…
Smartsheet Generated Document Updates
Good Morning. I have a Smartsheet generated document. If a field that is mapped to the document is changed, the document is not updated. Is there a way to make that happen?
Summary Fields for Dashboard based on dropdown values
Hi All, I have a rewuirement and, although I have a solution, it is very manual. I was wondering if there was a better andmore efficient way of doing what I needed. It is a question of reverse engineering in that my requirement starts with the end product a dashboard. On this dashboard I wish to show, in graph format, how…
Date Formula Question
I have a date request for each month by week assigned to their DCO’s hierarchy. 1st week of the month Brenda Van Roosenbeek, Daniel Farmer, Carolyn Monroe 2nd week of month Courtney Batey Coates, Sara Speith, and Lisa Funston 3rd week Julie Starry, Kerry O’Leary, and Becky Harper 4th week Alison Swetter, Scott Withers, new…
Timeline Calculation
Hello everyone, I am trying to write a function that gives below output: If date reviewed on or before the due date or within one week after the due date, the outcome should be "On-time" else "Not On-time" If date reviewed is blank and the due date has already passed the output should be No. of Days Overdue If date…
Formula to lookup a value based on start and end date
Hi All, I need your support on this case. I have a project to improve the current sales report layout and I need to extract the Vendor Support data for each item depend on the sales records, and the start and end date for this vendor support. Here is an example: Below is the table where I want to have the formula to…

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

Issues with Numbering Children (Child 1, Child 2, etc.)
Hi! I am trying to implement a column formula in Smartsheet that assigns an ordinal number to each of the children in a sheet. For example, the first child would get a "1", the second child a "2", and so on. I've tried several options, and the one I liked the most is the following: Create an auxiliary column with a "1"…
Netdays calculation with optionnal date column
Hi i'm looking for a formula to calculate the netdays between 2 dates. but i have 3 dates in the equation. a date column (date 1) whit references from another sheet and a manual one (date 2) to overwrite the first one. the third date is the "today" date (date 3). so i'm looking for something like: netdays between date 1…
Index/Collect from Descendants
In this sheet, I have a column [Groups] which is a parent column to multiple "Groups", each of which is a parent column itself to tasks associated with each group. I need help putting a formula in [Start Date] of the parent row that would give me the very 1st value from [Date] column that is a Descendant of [Groups]@row…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags