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.

Referencing data from other sheets
I need to create a sheet (let's call it Sheet 1) that references data from several other sheets IF a certain box is checked in those reference sheets. For instance, If the box is checked in any row of column B, Sheet 2 or column D, Sheet 3, the data from the Name, Date and Amount column for that row in those sheets would…
Move Rows to Another Sheet...that contain formulas
Hello! Has anybody had experience trying to automatically move a row containing formulas to a new sheet? I am trying to pre-populate cells with formulas that would apply on a different sheet so that they will be ready to go once moved over during a workflow.
Countifs a column with values and Invalid value error
I have a summary sheet that counts the number of not started, in progress and done tasks. This summary sheet looks up the name (index, collect contains) of the parent (Epic) of the tasks (Stories). Think Epics to Stories. The summary sheet has a column of Epic Names and a summary of not started, in progress and open…
Export with formulas intact
Hi guys! Is there anyway to export a sheet into excel and still have the formulas intact? Currently, exports only show values. Thanks!
If/Then copying cell information
I'm sorry if this is posted- I haven't found an example from previous posts that is working for my situation. I have 2 name columns- [First & Last Name] and [PI First & Last Name], and a third column for 'role'- [Study Role]. If the person picks the role I need (PI) I'd like copy the name of the 1st name column to the 2nd-…
How do I have the Status column automatically say "submitted" when a user fills out the form?
Currently it comes in empty, but I want it to say submitted.
counting the number of people that attended a meeting on any date
This discussion was created from comments split from: countif with dates.
IF and ISBLANK For Color Statuses
Hello All, I'm running into issues with my IF and ISBLANK formulas that will determine the color statuses. Here are my columns. Column 1- "Phase 2", this is a date field. Column 2- "Sell Date", this is a date field. Column 3- "RAMP", this is a symbols field, with the colors Red, Yellow and Green. Here's what I'm looking to…
Date + days remaining = auto generate estimated completion date
Hi! I have a sheet where I'm making projected completion dates. I know that for the total work remaining we have 88 days. Is there a formula to add today's date (dynamic) + 88 days (dynamic)? Thanks!
% complete on Gantt view
Hi there, I'm interested in adding the % complete option on Gantt view, like what is included in the Project Plan template. How do I associate % complete so that it shows visually in the bars? Thank you! Esperanza

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