Formulas and Functions

Archives for June 2022

How do I set up my sheet to reflect changes made in another sheet?
Hello! I am new to Smartsheet and trying to figure out how (and if it's possible) to set up a workflow or formula to reflect changes made in another sheet. Currently, I have one sheet set up that's linked to an intake form. I am using this sheet as a "master sheet" to track all submissions. Additionally, within this master…
Countifs statement with date range
I am trying to write a countifs statement using two references on another sheet to count the number or projects not in "blue" status and with a due date between 1-3 months - right now I have: =COUNTIFS({RAG}, <>"Blue" AND[{Date}, ">=Today()+31", {Date}, "<Today()+90"]) {RAG] is a reference on status to another sheet and…
Count: status is one of multiple criteria and due date is past
This discussion was created from comments split from: COUNTIFS function with multiple criteria.
How to roll a date back to the first of the month.
We have a calibration sheet that has a column for MONTHS (FREQUENCY), a column for LAST CALIBRATION and a column for CALIBRATION EXPIRY which uses the following formula to display the calibration expiry date. =IFERROR(DATE(YEAR([LAST CALIBRATION]@row) + ROUNDDOWN((MONTH([LAST CALIBRATION]@row) + [MONTHS (FREQUENCY)]@row) /…
Average Percentage for Parent Rows
My current formula is calculating the average percentage of parent & children rows. How do I get it to look only at level 1 rows? Here is my current formula: =INDEX(COLLECT([% Complete]:[% Complete], Level:Level, 0, [% Complete]:[% Complete], <>0, [% Complete]:[% Complete], <1, [Current Phase?]:[Current Phase?], 1), 1)…
I am trying to return a value based on a couple of criteria. I'm using the index/collect method.
I have 2 sheets. Sheet 1 has a list of names. Sheet 2 has a list of locations, dates, and who is there across 6 columns. My goal is to have sheet 1 list where these people are currently located. Sheet 1: Sheet 2: Formula for "Current Site" field on Sheet 1: =index(collect({Sheet 2-Column1},{Sheet 2-Column30},"Yes",{Sheet…
How can I index match (if) data is not found on first sheet to go to a second sheet fo find the valu
Ex. index (Code), match (EID@Row), sheet 1 EID But if ot found there I want it to look at sheet 2 for EID
Returning a Value When an Item is a Child of a Parent That Has a Specific Name
I need to create a formula that looks at all children (of varying levels) within a parent, and returns a specified name in a column for ALL children rows when the level 1 parent value is X.
Checking checkbox if the cell on the same row in a different column contains one of several values
Hi all, I'm currently having a little bit of difficulty writing a formula for a checkbox column. I intend to write the formula so that the box will be ticked if at least one of three values (people's names in this context) appear in a different column on the same row. I can get it to check by only specifying it contains…
Request assistance with a formula to pull only the first name from a cell
Currently I have a form which helps me capture the First and Last name of the user among other information. The first and last name is captured in separate columns. However, 1% of the user inadvertently enters their full name in the first name section. The way I am trying to get around this issue is to have another column…

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

Formula Help: Do This Week, Do Next Week
Hi, gang! I need to create a formula that populates Do This Week, Do Next Week, Do Three Weeks for my boss. I have Start and Finish date columns as well as a dropdown Do column with those three as options. We're not looking for reports, but rather this summary view in Card View for him. Thank you for your help!
Giving an output based on text from multiple selection dropdown
Good Afternoon, I've been racking my brain trying to get this function to work. I have a column set up to allow multiple selections of specific text with the column name "Processes." I want the cell to find the specific text and give a number value. I'll then pull from a "Due Date" column giving the processes start date…
How to use IF and IFERROR?
I am writting the formula for counting the result of the work of my team. The result is counting from the complete date< expected date would be green and if it is greater, it would be red. However, in some cases, even i did not fill in the expected date and completed date, but the result is still showed as green. This…

Formulas and Functions

Discussion List

Help Article Resources