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.

Trying to capture past dates and future dates
So, I have a column where I can predicted dates (to pay an invoice). Some of these values are past the predicted date of payment. Therefore, I want to make a formula that will capture all those whose date is in the past and those invoices whose predicted pay day is in the next 30 days. So far, I am only able to do :…
Formula Help: Switching Middle and Last Names in Text String
This discussion was created from comments split from: How to convert Full name to Last name, First and middle Name(s)?.
Index Match with multiple results?
We are tracking year end evaluations submitted by the supervisors for their staff, but when I use Index/Match, the only value returned is the first staff member reviewed by that supervisor. Is there a way to find and combine all results by supervisor? Thanks! Kristin
Why would the absolute reference change after sorting on a column?
I am creating a weighted score card. All my formulas appear to work as expected, with the exception of the Rank formula. Whenever I sort the sheet by either Team Member or Manager (both are setup as contact lists) the Rank function returns a #NO MATCH error for some of the rows. Prior to sorting, the RANK formula is:…
Returning cell based on 2 criteria in another sheet
Hi, I'm back! I am trying to pull the course cell per semester based on a student's ID and also on the Term (fall or spring) columns. This sheet is my "eligibility sheet" where I would like the course from my Active TAships sheet to go Active TAships Sheet: The formula I am using in the FA Acceptance (also will be in Sp…
Linked cell not working
I have linked my plan cell with another cell from another plan (it was task status btw), so it will serve as reference for my plan when my dependency task is complete, I can proceed with my tasks, but the content of this cell is not updating (the other links I did worked). What could be happening? thanks.
Move Row to New Sheet
I'm trying to figure out how to move a row that contains "New Trip Report" when a new row is added containing "Corrected trip report" and the Report ID's match. For example: see screen shot. Adding row 2 containing Report ID TR00001TX115 would trigger moving row 1 to a new sheet. If anyone knows how to do this, please let…
Problem with save as new and column formula
Hello, I have a spreadsheet that has some column formulas in it. I did a "save as new" and deleted some columns that had formulas in them in the copy of the original spreadsheet. For a reason that I don't understand, the formulas in the original sheet stopped working. I don't see how modifying the copy changed the…
Formula help needed: multiple combinations
Hello, I am in need of help with a formula. Basically, I have a formula looking at column A. Column A has 9 drop-down options, multi-select. Each of the 9 options has a specific (i.e., different) email associated with them. I have a large IF function that will accurately give the appropriate email, based on a single…
need help on a SUMIF formula to sum values if any of several drop down options are selected
I need a formula to sum all values in a column if that row has selected certain drop down menu options. The drop down menu is single select, but I am only interested in summing the values if the row has selected any of the first 6 options from the drop down menu. Here are the options in the single select drop down menu: 1…

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

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…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags