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.
Discussion List
Conditional Formatting
Hi all, i have a sheet that has a number of certification/ licence expiry dates and i have a conditional form at set up that highlights the cell based on date range. For a number of employees its set up correctly, as per the first image, but then for the remainder of employees i dont have the option to select 'in the…
Nested IF statement for multiple date range
Hello! I am currently attempting to write an all encompassing formula to read through different work windows. We are only able to work in certain areas of the city depending on the "work window". Depending on the work window, these dates have differing start and end dates. I can get one window to work properly, but the…
Average of the first 3 numbers and average of the 4th+ number
I'm trying to find the average of the first 3 months of data and then find the average of the 4th month and beyond of data. So for instance, the first row, the average of the red values and then the average of the green values. The start of data for every row could be different and the number total values of each row could…
How to verify cell contents as text vs number when numbers begin with 0/zero?
I want the following outcomes when a user enters a Supplier ID: If the ID is an ID found in the reference sheet: Associated supplier name If the ID is not an ID found in the reference sheet: "Check supplier ID" If text is input: Text input Here is my current formula - Issue is "ISTEXT" formula considers "000012345" as text…
COUNTIFS Date Formula and Formatting
I will try to explain this the best I can.... I am currently using data shuttle daily to populate a sheet. The imported data does not separate the date and time, so I am using formulas to separate them on my target sheet into separate columns. On a separate data sheet I am trying use a COUNTIFS formula based on a unique…
Formula to assign a number based on criteria
Hi All, I am looking for some advice here. I work with patients who are being scanned for research. Each scan takes place at an imaging site. I have data that is automatically exported in via our API with our platform that records the orders for a scan. The sheet automatically copies rows based on date to a new sheet where…
Alert recurrence that looks at a date field - possible?
I'm trying to set a recurring alert workflow to remind someone that they need to approve a submission. I have a "submission date" field and I would like a workflow that sends a reminder one week after submission date is reached and then weekly after that, as long as the status field is not changed to "approved". I was able…
Remove the need for duplicate reports in a dashboard
I have a sheet listing data from several different projects and would like separate dashboards created based upon the project data in the sheet. I can do this easily for one project and just add a filter for "Project = ##" to pull in a specific projects data. However I now have the conundrum of creating about 10 different…
Formatting currency with symblos
Hello, I am working on a smartsheet and need to know how to have to currency symbol automatically added based on the "Departure Country". I have two rows a Salary row and a Departure Country row. I need the Salary row to have the correct currency symbol based on the departure country which can be any country. PLease help…
Child Task formulas =(INDEX(MATCH()) in Control Center Blueprint Projects do not return results.
When a project is created using Blueprint via Control Center, the Child tasks have the Index/Match formulas located in the fields, but the fields are not returning any data from the associated sheet. This is working at the Top Level (ANCESTORS = 0), but is not working for any indented levels (ANCESTORS >0 ). Is this a…
Help Article Resources
Trending in Formulas and Functions