Formulas and Functions

Archives for March 2020

Functionality Question
Hello, I am more of an Excel guy who is attempting to wrap my head around Smartsheet. I have a scenario and I am thinking it can be done, it seems like I almost find the answer but just not quite. I have records which require attention, usually in contacting users and getting require, late training complete. There are a…
Sheet Summary Report Grand total row ?
I am bringing in 2 separate sheets into a sheet summary report using a custom column called "Total Revenue" which is a sum of each individual sheet's revenue rows The sheet summary report is showing correctly both rows for each sheet and the corresponding totals for each sheet. e.g. Miami $138,875 Tampa $264 My question is…
COUNTIFS in a multi-select contact column
I am trying to count the number of times a name appears in a multi-select contact column, however, none of the proposed formulas are working. Please advise. Thank you! Wendy
How to find a word and return a value
=IF([Trip Purpose]@row=(FIND"Morning"),"2-Day",IF[Trip Purpose]=(FIND"Afternoon"),"3-Swing",IF[Trip Purpose]=(FIND"Night"),"1-Grave", "Float") I'm trying to find the work Morning, Afternoon, or Night from a phrase in a cell in the Trip Purpose Column and then make the new column (Shift/) to return the corresponding value…
Missing Source Data on Dashboard
I was just in my Dashboard and saw all of my charts and graphs. Now, when I log back-in, I am getting a Missing Source Data on my Dashboard. How can I recover?
Formula from One Column to autopopulate other column dropdown
Hi Need help with a formula. When "Yes" is selected manually in the Incidents Safety or Medical column (this has a manual drop down of Yes or No, I would like it to If "Yes" selected the "GS&S Incident Case" Column to auto-populate with "Yes - Enter Case Number" and If "No" selected the "GS&S Incident Case" Column to…
Automation unsubscribed the creator automatically
Hi Guys. I encountered with something very strange in SS automation process I created, and I couldn't find a response to what I have been experiencing.I created a simple automation workflow that does the following: once the value in one of the cells reaches a certain value a mail will be sent to my personal mail. The short…
If Status = "DNR Request - Approved" or "MRS Request Approved" Then formula question...
In an effort to track the amount of time from submission to completion I'd like to have a formula help populate the [Actual Completion Date] by using the following logical expression...however I'm not getting this to work. If Status = "DNR Request - Approved" or "MRS Request Approved" then [Actual Completion Date] =…
Sequential Unique identifier for two types of records in a sheet
Hello Smartsheet community. I am in need of a solution that allows me to leverage an intake form to track Objectives and Key Results. I need to be able to sequentially generate unique ID's for each Objective and for each KR and I need this to not break with sorting. The reason is, I need to do lookups on the Unique…
Adding a Column in a Cross Sheet Reference Range on VLOOKUP
I'm exploring changing several of my more complex Smartsheets to rely less on links and more on data tables that are accessed by other sheets using VLOOKUP functions. Everything was fine until I needed to add a new column on one of the data tables and discovered that the named ranges that use that table don't automatically…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Is there a way to make a field in a form auto populate street address as its being entered?
I have a shipping management Smartsheet that has a intake form with a address field. When users fill in the address field sometimes they do not provide the entire address. I want the address to auto populate as they type in their address.
IF formula for INDEX/MATCH across sheets
Hi, I'm really struggling with this one. I have a sheet that has a drop-down with options for "Yes" and "No" and I want to perform an INDEX/MATCH function based on another cell in that row, but only if the result of the drop-down in that row is Yes. The sheet reference for the Yes/No drop down is {App_S}, and the two cells…
NETDAYS Formula Question
I have created a formula to track in progress orders by the number of days, =NETDAYS([Date Received]@row, TODAY()) I have the following columns created, Date Received, Date Completed, In Progress Days This is a formula attached to the entire column, but I need cells for orders completed to stop counting as in progress. Is…

Formulas and Functions

Discussion List

Help Article Resources