Formulas and Functions

Archives for August 2020

COLLECT date values from another sheet
There are 2 sheets with the same set of columns: Code / Crop / Start / End / Cost Start and End Columns are dates. 1) I need to collect date from column Start which satisfies the conditions Code = 1122, Crop = Apple. The formula is the following: =COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, @cell = Code@row, {Sheet1 Range…
Use Status change to create a child record
Hello community, We are working on a new process flow for developers. Each row would be a project and as they move the project through different stage(Column) it would create different subtasks(Child records) to each stage. I don't want to allow them to change the stage until they have completed the subtasks for the stage…
Automatically Update a date when it's in the past
Hello, I'm trying to create a formula or workflow rule that updates a date column when the date is in the past. I work on annual renewals so I want the renewal date to change from 9/1/2020 to 9/1/2021 after 9/1/2020 is in the past. I was able to create a formula in another column that adds 365 days to the renewal date but…
Automate (100%) in % Completes column based on date entered in Complete Date column
HAPPY FRIDAY EVERYONE All, I need some help with this formula The goal: when a Complete Date is entered the % Complete Column will update to 100% The results have been unfavorable =ISDATE([Complete Date]45),([% Complete])="100%") This one gives an unparsable =IF(ISDATE([% Completes]45), "100%") This one doesn't give an…
Source Sheet v/s Destination Sheet
@Paul Newcome or @Andrée Starå Can one of you possibly review the discussion linked below and provide your thoughts and suggestions? I've been trying all of the recommendations received thus far and they just aren't working. https://community.smartsheet.com/discussion/comment/261225#Comment_261225 Thank you both in advance…
Triggered > Move error
Row's cant be added to the destination sheet because the primary column has the same name as a non-primary column on the source sheet. < How to fix this ?
Can I change case AFTER the cell is referenced?
Hello, I have a Master sheet with hospital unit names. Some of the names are all caps, and I wanted to change that to be first letter in caps only. The words themselves would be the same. Only the case would change from capital letters to normal letters. The cells where these unit names are are referenced in formulas. Will…
Reduction of Column Count from dynamic form
Has anyone found a way to reduce the column count from a long dynamic form? I was thinking of using conditional logic on a separate form with a copy workflow to move data, but I'm not confident it would be reliable. I'm running some tests now, but if anyone already has a solution I would be very happy to hear about it.
Counting Checkboxes to deteremine Probability
I have multiple check boxes in a row and want to determine Probability based upon how many of the are checked. If 3 checked its High, if 2 Medium one is low. am doing this across sheets, other words metrics sheet that looking at with boxes..
IF(AND ......formula help needed
I am trying to add one more argument to my formula below to return a green symbol "if a task is underway and not due within the next 30 days...otherwise leave it blank". This is so I can easily pull the task into a summary report. The first formula below works fine but when I try to add (see third formula) is says…

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 FORM ID?
I have multiple forms that can be used to submit data to a worksheet, and I'm wondering if there is a FORM ID that could be used in a column so I know which form was submitted. If yes, is there a specific formula I can use in a column to identify the Form ID and Name of the form?
Index/Match Formula Behavior
Hello, I'm encountering an issue with the INDEX/MATCH function. When the value in the cell being match does not find a match and is recognized as text rather than a number, the formula does not return 'no match' as expected. Instead, it displays the last entry in the reference range. Formula in cell Category Index/Match…
"Boolean Expected" Error
Hello All, I spent a few hours building an equation (I am not an expert) within a sheet that reads another main sheet. =IFERROR(INDEX(COLLECT({GenFertCalc}, {Client Name}, [Client Name]@row, {Tones}, "Granular Fertilizer (Tone)"), 1), 0) The error message "Boolean Expected" occurs in the correct cells, but instead of…

Formulas and Functions

Discussion List

Help Article Resources