Formulas and Functions

Archives for May 2023

Formula for projects completed by month
Im looking for a total # of projects that are completed by Month and year
Look up Contacts from Cell on Sheet
I've got a bank download that has a user name in "Last, First" format and I'd like to look up the Contact so I can e-mail the data to that contact. Any suggestions? Appreciate it! Brent
Formulas not properly updating when referencing from another sheet
Hello! I have been working with a set of formulas that do not appear to be calculating correctly when I am referencing that sheet from an external sheet. Here's my problem: In my sheet, I have recorded a "created" column that uses smartsheet's auto generate date function to record the date that the line item was made. In…
How do I hide the resulting zero from SUM IF formulas?
I am currently working with the following SUM IF formula, and I need to hide the results of 0 to declutter the sheet. This is my current formula: =SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy", {Title V Data Range 1}) I tried this variation: =IF(SUMIF({Title V Data Range 2}, "XALD adrenomyeloneuropathy",…
IF formula , with item found on another sheet
Hi, I am trying to create a formula. i want to return a patient's status based on if that patient is found on another sheet. I have 2 sheets list of active patients available cases 7.6 I have a an admission ID on both sheets which can be used in a vlookup type of formula. if the admission ID is found on the "available…
Trying to lookup most recent
Hi New to smartsheet So my goal is to lookup only a value from the most recent row in another sheet and preform a calculation Example. What I am trying to build is a countdown to Preventive maintenance for some of my fleet vehicles as entered on forms The idea is when row 1 on this sheet is updated (oil change preformed)…
Need Help with Formula, want all project summary sheet to record most recent step completed
Hi I have a summary sheet that contains all projects that my various PM are working on, the project summary sheet looks like this: Each project then has its own sheet for the PMs to keep track of what steps have been completed for the project, looks like this: On the summary sheet in the "most recent step completed" column…
Help with Harvey Ball formula
I have both "start date" and "end date" columns in my sheet, as well as a "status" column with yellow, red, green, and gray harvey balls. I want the color of the harvey ball to turn to red when it is past the end date. Is there a formula that can do that for me? I only want to automate red harvey balls, not the other…
Calculate when Status is New and Recipient has received minimum of 1 of each type of Award
Hello, I have a Smartsheet for team recognitions, that has column Recipient (dropdown list with approx. ~100 contact names), Recognition Theme (drop down list with 4 themes) and a column for Status (dropdown with ‘New’ and ‘Processed’). I am trying to build a formula to calculate when Status is New and a Recipient has…
How can I connect multiple sheets into 1 master sheet?
I have multiple sheets, each populated by their own form. I would like each sheet's columns imported into the master sheet and populate the same row. Is that possible?

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

How to reference column name pulled from formula
I'm trying to pull a date from columns based on the number in the helper column. I know I can do this with nested IFs, but is there a way to change which column I'm pulling the value from? My column Start Date should be whatever the column is based on the Index. So for the first column below, since Index is 3, it should…
Conditional Formatting Based on 2 Date Columns, with or without Helper Column
Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06/29/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06/29/2023 deadline,…
Unique IDs that combine a field and sequentially increase
I'm creating a change management log that allows users to submit change orders via a form. To simplify this question, I would like the user to include the project number in their request (this will populate a sheet column titled "Project Number"), then a formula in the sheet (in another column titled "Change ID") will…

Formulas and Functions

Discussion List

Help Article Resources