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.

JOIN/COLLECT Duplicates
Hello Smartsheets, I was looking for some advice using JOIN/COLLECT. If I'm using JOIN/COLLECT to pull from a column that has repeat instances of the same value (in my case vendor names) is there a way to only show one instance of this name on the sheet they are being displayed on? So that I do not see the same name shown…
Parsing multi entry cells into rows
Hi, I have a sheet (with form) that collecrs "activities" at a coffee plantation. When busy witha new FORM submission I select the activity (dropdown, single select), start and end date and the staff involved (dropdown, multi select), This leaves me with a "activity 1 staff" cell containing up to 100 names. I'd like to run…
Column Formula to Leave Child Row Values Alone and Average or Sum them in Parent Row
So far I have been able to get three formulas to work, which include other formulas, but now I am having an issue pulling an average or a sum when the child rows do not have any formulas embedded. The following work when I convert to a column formula: For a Sum: =IFERROR(IF(C@row > 0, SUM(CHILDREN()),…
Vlookup
what is the best way to look up between smartsheets and keep the data fresh =VLOOKUP([Equipment ID]@row, {Equipment Tracker H5 RM 8.8.24}, 1, false) Equipment ID]@row = equipment id this sheet {Equipment Tracker H5 RM 8.8.24} = large data block in another sheet this works but will not refresh if data changes help
If statements
I'd like to create an if statement to automatically check a box if a status column has any of the 6 drop down options selected. This is the first step in an exercise to try to tackle parent/child columns in reporting but need to crack this step first. Thanks in advance
Returning a PASS FAIL based on percentage score results
I am trying to return a Pass Fail text based on the percentage of a total score. I am creating an audit form and based on a final score (total points/available points). I am using the below formula but for some reason no matter what the score is, I am seeing a FAIL response. Score must be 90% or higher to pass.…
Mailto: in a sheet
I am trying to create hyperlinks to email addresses in a sheet. I’ve tried two methods. First, was putting the email address in one column and then using a formula to add “mailto:” in front of it. This is adding the text but they are not clickable. I also tried using =HYPERLINK("mailto:" + [Email Column]@row, [Email…
Index formula help with a global update when referencing template metadata sheet
Hello! We are using the Control Center. In my template folder, I am adding an INDEX formula to a sheet that pulls information from the metadata sheet. It works great, and in my next provision, it works! Viola! I love it. However, when I try to do a global update, the formula fails to redirect to the project-specific…
Organizing Data in a Pie Chart
Hello everyone! I created a Pie Chart widget from a report on my dashboard but I am not able to organize the order of the pie slices. Currently its presented in ascending order from left to right and I would like to organize the pie slices in a different matter. I would like for the pie chart to be in ascending order based…
Removing blanks from a JOIN COLLECT formula
This discussion was created from comments split from: Need help removing blanks from a join collect formula.

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

NEED TO ADD DAYS TO THIS FORMULA IF ANOTHER CELL HAS SPECIFIC ITEMS OF A DROP DOWN.
I have this formula that works for this cell when information is missing from reference cell A. However now we want to be able to add additional days if reference cell B has specific items checked in a dropdown. I just can't seem to get it to work. Any suggestions. =IF(ISBLANK([ ENGINEER (tentative start date)]@row), "",…
Check a Box if Between Dates on Another Sheet
Hey all, I am trying to build a formula to check off a box on my sheet if at least one date is found between two dates on a separate sheet. I've tried a million variations but here's what I have so far, thinking I was overcomplicating things =COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row,…
How to Automatically Checkmark a Column for entries that are created within a time-based timeframe
I'm trying to write a formula that will automatically place a checkmark in a checkbox column titled "Today Entry" whenever entries meet the "Created" column criteria of being submitted after 1100 AM the day prior and 0800 AM today on a rolling basis: I've been trying to use the below column, but honestly I have no idea. I…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags