Formulas and Functions

Archives for September 2018

if /or argument
Sorry to bother with such a simple question, but what is the problem with this? =IF(OR([Average 6mths]7 * 1.5 > [Current Monthly Usage]7, [ Average 6mths]7 * 0.5 < [Current Monthly Usage]7), 1, 0) I am just trying to return 1 if between 50 and 150% and 0 of outside that range
Formula for checking Parent Checkbox when ALL Child Check boxes are checked?
I'm trying to find a formula that can be placed in a parent check box cell, so that when ALL child checkboxes are filled in, the parent check box then becomes checked. I have multiple columns this is needed for, so ideally looking for a solution without having to create a number of other columns to draw calculations from.…
Moving children rows under a different parent row
Is there a way to set up a formula that will move project status "completed" from under one parent row to another parent row named "Completed project" once the project status has been selected as completed?
Commission tracking
Good morning Smart Sheet community. I have been tasked to create a commission tracking smart sheet for our sales team. If you have a pre-fab sheet(s), and willing to share, I would be forever grateful. Otherwise, here was my thoughts on said sheet. First sheet would be for the actual sales. This would have a form available…
Checkbox tick then auto highlight certain columns?
Hi, We have a sheet called project milestones for managing the steps of LIVE manufacturing an installation projects. I'd like when our production manager selects a tick box on a specific row, that 4no columns within the row are automatically highlighted light green. The column "In production/ Approval Docs uploaded" Should…
Nested IFs- mutually exclusive
I have a nested IF formula to change the status ball colors as values are entered down the line. They usually get entered one at a time. If column A gets filled then it turns green, then if B gets filled it turns yellow, etc.. Column A | Column B | Column C Green | Yellow | Red X | X | X But sometimes we will skip over…
Reference the Children of the Parent of a Cell as the Range
I'm looking for a way to auto number my WBS. I believe I have found a way using logic functions combined with the Match function. The issue I am running into is that I am unable to reference a cell, say Task5 and then specify its "siblings" as the range. I have tried to specify the range as Children(Parent(Task5)), but I…
Calculate Age from birthdate
Is there a way to have a cell auto calculate age (years and months) from birth date in another cell?
Percentage of Child Rows with Check Mark
Hi, I'm trying to get a Percentage of Child Rows with Check Mark. Can anyone help out? =COUNTIF(CHILDREN(), 1) + "" returns the count of checked boxes. =(COUNT(CHILDREN(), 0) + "" returns the count of total rows, check or unchecked. So I divided the two, but I get #INVALID OPERATION error. Any thoughts?
VLOOKUP data type question
I'm using this vlookup formula to pull back a value from a data sheet. Let's say the value of NUMBER81 is 123456 and when I type in the formula on the desire sheet it returns "No match". When I go to the ACCOUNTS sheet and look at the data there is a value equal to 123456 in Range 1. When I put an apostrophe in front of…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Match / Sumifs formula
Hello! I am looking for a formula to match the entry in sheet 1 to the entry in a sheet 2, then sum a column in another sheet 2. Would a match or vlookup formula work best? 1 - Match Primary, Tower and Blade columns in sheet 1 to Site Name, Turbine Number and Blade Number Sheet 2 2) Return sum in Travel Hours column from…
Extracting Multiple Data from one cell into multiple cells - same row
I have a smartsheet where I'd like to extract data from the LIST ALL occupants column and place them into Occupant 1, Occupant 2, etc. out to 13. I have this working for columns 1 and 2, but then it repeats, even though I change the +# in increments of 1… and if there are no additional names, I'd like it to be blank.…
IF Formula working on some and not on other items in column
Hi All, I have a formula running in a column to validate an action based on whether it was done prior to 01/01/2011 or not. =IF([Install Date]@row >= 1 / 1 / 11, "Yes", "No") The formula works as required on rows where there is a year only, but throws #INVALID OPERATION where a date has been entered through smartsheets…

Formulas and Functions

Discussion List

Help Article Resources