Formulas and Functions

Archives for October 2022

simple date range question
Hello, I feel silly for asking this, so I hope this is easy. I cannot find a simple answer on this anywhere. Every question I read date ranges is about some complicated calculations with other data etc.. I simply want to have a formula that either combines two dates to show a range or calculate a range based on just adding…
Track Project Changes and showcase the ripple effect
Hello SS Community :) I was wondering if anyone knows of a way to track project changes and more so how to showcase the ripple effect of that change. I guess sort of a what if analysis, if I pushed the date back on this task how would that affect the overall project or what part of the project would be affected? Would this…
I have a conditional format sorted. When certain dates get above 24 hrs, then the box turns green.
QUESTION: Is there a way to change the text in another column when this date is met? IE: The Process is now complete?
Copy Date from one sheet to another if criteria matches
Hello all, I have a master project sheet and I'd like to copy the date from another sheet into it. Below is my master sheet and in the Milestone Date column, I want to copy the End Date from Project Sheet (below), if the "Primary Column" name from the Project sheet at each line matches the "Project Name" line name in the…
Formula for Checkbox
I would like to have a checkbox appear in my project plan, only when a certain condition is met. ex. a Y in a helper column Does anyone know how to do this? Tx, Tom
When status changes in one sheet, how to get it to cross reference into another sheet?
Hello, I am trying to create a workflow when the parts status changes to "ready to schedule" in the Parts Log sheet gets copied into the sheet Project Management. I was able to create this workflow. The problem I am facing is that every time the status changes it gets duplicated into the Project Management sheet. How do I…
How can I find a position of a character in a string?
Hi all - I'm trying to find a position of a character within a string, which in theory seems simple - just use the FIND() function. What I'm struggling with is when there are multiple instances of the value, and I want to start at the 4th instance (for an example) of it. Here's an example of the String I'm trying to parse:…
How to Add Specific Field into Form with SUBSTITUTE Formula
Hey Community, I am trying to have my OPPTY # field prepopulated in my form using the SUBSTITUTE formula because a lot of our users tend to enter it incorrectly. Can you please help me figure out how to solve this? I will paste a screenshot of the format below.
Workflow Question
I have setup automation to request updates each week. Some people don't have any changes to update. How do I know that they submitted their update with no changes. In the activity log I only see the people who made changes to the update.
Linking status to Summary Sheet
Can someone help me with a formula? I want to say if Name on project sheet = name on summary sheet, then give me the Health. I've tried a few things and keep getting errors.

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

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…
How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…

Formulas and Functions

Discussion List

Help Article Resources