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.

How to Combine Multiple Formula for Matrix Table
Hi All, As we know, not all formula can be used in Smartsheet. Need help on combining "IF" "OR" "INDEX" & "MATCH formula. Currently we have a risk rating matrix where it depends on user selection on column "Consequences" & "Likelihood". Formula I apply in excel are:…
How to auto update field value in a summary sheet?
Hi, I am using a formula to caculate the "Elapsed days" in the red circle of the Summary sheet as shown below , and display this value in a Dashboard. The problem is that this value "Elapsed days" is not auto updated daily in the dashboard as i expected. What I have done is refresh the sheet then save it. After this, the…
Multiple criteria in a row for VLOOKUP
I am trying to create a report from data with multiple criteria in a row. The objective is to return the residual risk value for each combination of risk + strategic initiative. In Excel, I was able to accomplish what I am looking for with this formula (=VLOOKUP(CONCATENATE(D$2,"-",$B36),'FY 2020 ERM…
#ERROR
Does anyone know how to resolve the #ERROR? I saw previous posts saying its a problem with VLOOKUP or cross sheet formulas, but mine is on a simple formula on other columns of that same page. It was working fine all day, then suddenly updated to #ERROR. Will that go away on its own? I've tried refreshing the sheet, closing…
Index Join formula issue
Ok, I've been sitting on this for a couple hours and I just can't figure out what I'm missing. I have a column formula that is selectively working on certain rows, but there is no obvious difference between those rows. The formula is: =INDEX(COLLECT({APStepCode}, {SubcategoryID}, [SAP Subcategory ID]@row, {VBC ID List},…
Row Copying Issue
Hi I have a sheet, and in it there's a VLOOKUP to another sheet. I set a Copy Row Automation so that when the vlookup cell is filled, it should Copy the Row. I setup the Wrokflow like this: Trigger: When a Row is Changed When: <Field> Changes to Any Value Run Workflow When Triggered So, everything pulled into the sheet…
Check Box function not working
For some reason today I am unable to get a check box to work in a new sheet or even add one to an existing sheet.If I create a column and select "Check box" in the column properties, the empty check box doesn't appear. If I add a "1" to the cell then the "checked" check box appears.Any sheets I have with existing check…
Formula Issue with Symbols
Hi I used to be able to create a formula for a column that had a symbol that would automatically populate for example if Level="Milestone", "Yes","No" and this would automatically put a Star in the place where the task was shown as a milestone. This now only shows the word yes. I have deleted the formula and then converted…
How do I return Last Match with VLOOKUP
Hello, My VLOOKUP function is always returning the first match other than the last match. The lookup table is updated chronologically, and I need to return the most recent data which will always be the last match. I'm open to other solutions that use other functions. Thanks.
Multi Page Formula dragging:
I am working with multiple data sheets where my formula draws information from the same cell in 4 sheets, i.e. =SUM({pg1 cell1}, {pg2 cell1}). The formula works correctly, but when I try to tag and drag or C/P the formula into the rest of the cells in the column, it simple duplicates the same original cells, rather than…

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

Date format Formula
Hi All My Date column is formatted to YY/MM/DD - 2024-11-08 I have a week-ending column with the following formula. Returning the End day of the week that the Date column falls into =IF(OR(ISBLANK([Date]@row), [Date]@row = "N/A"), "", "WE- " + ([Date]@row + (6 - WEEKDAY([Date]@row)))) BUT the return looks like this WE-…
No Match...but the formula works
Can someone help me with this? I have a formula which works but it's finding NO MATCH for two lines but I don't know why as the formula has no problem finding a different line on the same sheet… This is the formula: =IFERROR(IFERROR(IFERROR(INDEX({IQA Log Record Closed?}, MATCH([Primary Column - IQA Ref]@row, {IQA Log IQA…
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…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags