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.
Discussion List
automate RYG balls
I'm a smartsheet newbie and i'm ultimately trying to build a formula that automates task progress based on the following: If % Complete = 100, return Blue If it’s not scheduled to start yet, return Clear If it’s past the scheduled finish date, return Red If % complete >= (today date-start date)/task duration, return Green…
Formula Help
Hi All, I am in need of some formula help! 1. I would like to count all of the projects in my portfolio that are in the Active state and either Yellow or Red health. I have a Dropdown List column for State and a Symbols column for Health (RYGB balls). This is the formula I've have been trying. I am getting the Unparseable…
Cross sheet formula - Reporting by week
Hi Again! Is it possible to create data that provides QTY of selections checked per week and record this on a sheet automatically with formula. So we are managing live installation projects, with a high turnover of projects. Our main sheet is called PROJECT MILESTONES where we track all the live project activity. Within…
COUNTIFS or other appropriate formula
Hi, I'm attempting to create a count of the occurrences of text in a column called 'DOCTOR' from a list of drop downs in rows 2 to 32 inclusive: FULL DROPDOWN LIST: CLINIC 1 CLINIC 2 CLINIC 3 NOT ROSTERED ON COMPANY BUSINESS UNPAID SICK DAY UNAVAILABLE I'd like to get an automated count of the instances of the following:…
Card view is deleting my formula
I've created formulas to automatically calculate status based on start/end dates and % complete. If you switch to card view and move things within this view it deletes my formulas. How can i lock users from card view or what are my options here.
Auto Filling a cell from data in another samrtsheet
Good afternoon I am creating a bid invitation sheet and need to list GC bidders per each job (row). * How do I create an autofill for these cells based on a directory I have created? * and once the GC is selected in this cell how do I autofill the column next to it with one of their many estimators at their company that is…
Formulas per column
Hello, Smartsheet team: If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is there a shortcut that will apply a formula to the entire column, or to a selected part…
Status and % Complete co-dependency formula
Hi there, I am trying to figure out how to get Smartsheet to automatically change "status" column to "Completed" when I enter 100% in the "Complete" column, as well as to "Started" when I enter any % higher than 0%, and to "Not Started" if the % is 0. Is there a way to do this? Thanks!
Formula needed that will generate quantity in one column against date in another column = how much a
I have the following columns and need to return the value of something from a date through the end of the year. 1. Est Mo Contract Count - A number 2. Ave admin - Dollar Amount 3. Estimated Value - 1 x 2 4. Start Date - Start Date that 1 actually goes into effect 5. Weighted Value - Sum of 4 through end of the current year…
Determining duration in Hours:Minutes
Hey guys, I have read several posts with several workarounds for calculating a value between a Start Time and an End Time where the input and result need to be in HH:MM. I have an idea and a beginning: 1. I created two System Columns. [Start Time] as 'Created (Date)' and [End Time] as 'Modified (Date).' 2. I created a…
Help Article Resources
Trending in Formulas and Functions
How do I get a cell to display the value of the last cell in a column excluding one of the values?
Hi All, I have a column in a project plan with a droplist of certain values. I want to display the value of the last cell in the column that has any value except Not Started. Would someone please provide a suggestion?
Replacing NULL in a column with zero
I need a formula(s) to use that can replace "NULLS" in a worksheet with zero or replace nulls with "Blank". One column contains text values and the other column number values A formula to replace "NULLS" with zero in a number value column Another formula to replace "NULLS" in a text value column with "Blank"
Form: Require "Need order by" field to be AFTER today.
We have a form that has a DATE field called "Need Order By" so that the user can tell us the date the the order must be fulfilled by. Can you guess what date users MOST OFTEN use? Yeah, you got it! a date in the PAST. We are very anxious to find a way to force the user to input a date that is in the FUTURE. Can anyone tell…
