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.

Sequential Numbering Autopopulate Row
Hi all! I am trying to create a formula where every time I add a row, it aligns with the next number in the series. If it reaches a level 1 row however, the series goes to the next whole number. I wrote out manually what the numbers should be but I would like a formula to apply this automatically when i add a new row. Im…
Cross Reference Formula
I am looking for a formula that will cross reference between 2 sheets. I have 2 sheets, 1 sheet is the master data. I want to cross reference so when something is input on my 2nd sheet that it goes to the Master sheet and determines if the Customer number and the Email exists on the master data and inputs Yes or No. The…
Flag #CIRCULAR error
Hi, I am attempting to create a formula that will check the At Risk (Flu) if it meets certain criteria. If Flu Compliance Status is "Non-compliant" and Level of Flu is grater than 5% check At Risk (Flu). The formula I created with a #CIRCULAR error is listed below. =IF(AND([Flu Compliance Status]@row = "Non-compliant",…
Need formula to retrieve data from another sheet using multi-select field as reference
I need to find a way to reference/pull data from another sheet, using details listed in a multi-select field. Here's the process and what I'm trying to do: Customer submits a form to sheet 1. The form contains a multi-select "Product name" field. example dropdown menu options: Audio book Audio book fiction Audio book…
Is it possible to dynamically reference Sheets from a cell value?
Hey, hoping it might be possible to get a little help here as the search feature just gives me 1000 permutations of 'link to the other sheet.' What I'd like to be able to do is link to a sheet named in a particular cell. So if Column 2 Row 1 says 'First sheet' and Column 2 Row 2 says 'Second sheet', I'd like the same…
Join Collect from Other Sheet only IF not blank
I'm looking to collect all project updates in a single cell with a running history. The information lives in separate lines in a sheet: Current Formula: =JOIN(COLLECT({Timestamp + Note}, {RowID}, [Project ID]@row), CHAR(10) + " || ") Update needed: "SKIP" rows where the Timestamp + note is blank. I tried adding a criterion…
Conditional Formatting Rules not working
I have a formula in my sheet that calculates Actual Duration. (This is in addition to my Planned Duration column, for which I use Smartsheet's default duration capability to calculate). I am trying to enable a conditional formatting in my sheet that turns a cell white (text not visible) when there is an #INVALID DATA TYPE.…
Populate Column With Unique Values From Another Sheet
Hi all! I have a sheet, where tasks are entered and assigned to employees. Every task belongs to a certain company, which is specified row by row (every row is another task), this describes the first sheet we'll call "Requests". I want to create another sheet to summarize the data of every task for every company, this…
Less than or equal to 30 days from today
Hello, I'm trying to figure out how to finalize this formula to be correct but I'm having issues. I want it to count if the date from another sheet is less than or equal to 30 days from today. However its counting dates prior today. I feel like its something simple im missing but just need some help. See formula below.…
Forms branching
Do Smartsheet forms support question branching to reduce the number of question an end user sees? Thanks Becky Menning BJC Medical Group

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

Can anyone tell me what's wrong with my formula?
I'm trying to set up a project plan that has color indicators for the timeline: RED=past due, Yellow=due in the next 5 days, Green=due in the next 14 days, Blue=due later. =IF([Due Date]2 < TODAY(), "Red", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 < TODAY(+5))), "Yellow", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 <…
Help with a #UNPARSEABLE error
I want to reference the values in a column from another sheet (2025 IOE Procedure Assessments Equipment). And if any of them match any cell from the Equipment column from the sheet I'm working in then, I want the Audit on Equipment Completed column to return a "Completed" value. This is my formula. What is wrong with it?…
I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags