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.

Reusing the same automation on the same triggered cell only works once
I have a simple automation - If a Status cell changes to red or green then trigger. It works fine unless I try to trigger it again by forcing a change on the cell. I thought it was because cells are locking but I unlocked them and the problem was still there. "Status" is set by checking the values of a group of other…
Using #INDEX & #MATCH cross sheet reference formula help
I am attempting to write a formula that says: IF {in sheet# 1} COI Type = "Job" OR IF Agreement Type = "Project" OR IF a row matching the code in sheet #1 matches a code in sheet #2 AND Waiver Type = "Approved-Job Specific" THEN check Approval Required box. The first part of my formula works: =IF(OR([COI Type]@row = "JOB",…
Use Status change to create a child record
Hello community, We are working on a new process flow for developers. Each row would be a project and as they move the project through different stage(Column) it would create different subtasks(Child records) to each stage. I don't want to allow them to change the stage until they have completed the subtasks for the stage…
COLLECT date values from another sheet
There are 2 sheets with the same set of columns: Code / Crop / Start / End / Cost Start and End Columns are dates. 1) I need to collect date from column Start which satisfies the conditions Code = 1122, Crop = Apple. The formula is the following: =COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, @cell = Code@row, {Sheet1 Range…
Current Week/Last Week
I want to be able to get a count of Issues Created, based on certain criteria, for both the current week and the week previous. I tried making things easy for myself and creating helper columns but those didn't seem to function, so I am here. I want to count the number of instances an issue was created if the branch and…
Referencing a not blank cell of 5 cells.
Hi, I am trying to populate a cell with data in 1 of 5 cells that are filled from a form. For example, using form logic of the organization that is selected (column Org Name) select the site from that Org (Org 1 and site 1) Each Org has its own list of sites in its respective column. I have tried using IF(ISBLANK(NOT( and…
Auto Populate from Forms
Hello everyone I have created a form to gather data, from there i the sheet i've created an automation out of it to show in my dashboard. My problem is, i could not find a way that a certain column with formula updates automatically without dragging it? Here is my formula: =IF(AND(ISTEXT([Row ID]2), ISBLANK([Logistics…
If and symbols
Hi I have a symbols (rggy) column "Overview Status" that looks at two columns Expiry Date and Overview Date. I would like that if expiry date is less than today to be gray, but if the overview date getting close to today to be yellow, red if it's late and green if it's OK. I would like the yellow warning to be a month…
Source Sheet v/s Destination Sheet
@Paul Newcome or @Andrée Starå Can one of you possibly review the discussion linked below and provide your thoughts and suggestions? I've been trying all of the recommendations received thus far and they just aren't working. https://community.smartsheet.com/discussion/comment/261225#Comment_261225 Thank you both in advance…
JOIN Formula from Source Sheet to Destination Sheet
Please help. I'm able to join/concantenate text from different columns in the source sheet to one column in the destination sheet. However, text from different rows in the source sheet is populating in the same cell/row in the destination sheet. Please see the screen shots and formulas below. SOURCE SHEET: DESTINATION…

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