Formulas and Functions

Archives for November 2019

put current date when checkboxes are checked
Hello, I want to configure a formula to set the current date when 2 checkboxes are checked. I have configured 1 date column and 2 checkboxes columns. I don't want to use the auto-modify date column for this purpose. Please help.
Auto assign based on value in Field
I would like to auto assign and update the Assigned To field based on a value in another field. I've tried something like this; IF([Contract Type]=NDA, John Doe) So if Contract Type equals NDA, then put John Doe in the Assigned To field otherwise keep blank. Can someone assist?
Ignore Parent Records formula & Ranked graph
Hello. I am trying to create a graph that shows the top 10 tasks ranked by the variance between Initial Duration and Actual Duration. To make this work, I want to ignore tasks that are parent records. What would this formula look like? I have created a formula to rank the Variance; =RANKEQ(Variance1, Variance:Variance, 1).…
Dates in formula AVERAGEIF
Hello, I have problem with the dates in the formula below (in sheet summary) =AVERAGEIF([Date of request]:[Date of request], >07.01.2019, [Completed in days]:[Completed in days]) The Smartsheet returns me the value 0 that is wrong cause I have lots of numbers that meet this criterion. Please advise and thanks in advance
Finding the free project dates
Hi I am running a number of tasks in a sheet, with each task having its own Start and Finish date. What I need to find out, is a "free" period when there is absolutely no task running. Example: Task A is from Jan 1 to 10; Task B is from Jan 5 to 12; Task C is from Jan 16 to 20; Task D is from Jan 15 to 25; and so on.... In…
Creating a PMO functionality
I have just been assigned the task of setting up the Project Management Office in my company. For now I need two things, firstly a project tracker for tracking and monitoring individual projects and then a master portfolio tracker where all the info that's updated in the individual trackers feed into. This master tracker…
Hot Tip: INDEX/MATCH Formula "Shortcut"
Copy/paste this to quickly build these formulas: =IFERROR(INDEX(ReferenceSheetPickContentColumn, MATCH([CurrentSheetMatchColumn]@row, ReferenceSheetPickMatchColumn, 0)), "") Then just double-click the concatenated-word segments, replace the outer ones using the ReferenceAnotherSheet link from the Formula Help panel that…
Formula for RYGG symbol based on RYGG symbols in 6 other columns
balls. The 6 other subcategories/column names are: * Financial Status * Quality Status * Staffing Status * Delivery/Operations Status * Customer Satisfaction Status * Subcontractor Management Status The Overall Status is green if all subcategories are green. The Overall Status is yellow if 1-3 subcategories are yellow. The…
VLOOKUP to Return Formula?
How can I use a VLOOKUP to return a formula instead of a string? How should that formula appear on the source sheet? I've tried putting it in quotations then extracting with FIND, but no luck.
Problem in taking the weekof as Wednesday
I want Wednesday as the weekof based on the date for e.g. If cell A has date of 25th Nov, the weekof in cell B for that date should be 20th Nov If cell A has date of 27th Nov, the weekof in cell B for that should be 27th Nov If cell A has date of 26th Nov, the weekof in cell B for that should be 20th Nov What would be the…

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!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

Watch video

Trending in Formulas and Functions

countifs and a date that falls between two dates
Please help! I have two formulas that I am struggling with. I am trying to count how many tasks have been completed/closed within the past fortnight. I came up with the following formula but it gives me the 'invalid operation' error. What am I doing wrong? Formula=COUNTIFS({LV_ BLD CONST QUERY REGISTER _status}, "closed",…
I am trying to compare 2 date columns with an odd result
I am trying to count the number of submissions on a daily basis with =COUNTIF({Loans Range 1}, =[Begin Date]@row). Most of the formulas return the correct number of submissions. What it looks like it is doing is applying the submissions entered after a certain time (5 PM?) to the next day. Dependencies are disabled…
Revise Formula to include a but
My current formula in one column that is working out is the below: =IF(OR(CONTAINS("Term", [Column1]@row), [Column2]@row = 1), "Green", "Yellow") For the next column I need a revised formula to say "if the term is in column 1 and column 2 is check BUT Column 3 is checked then it will give me a red ball" What do I need to…

Formulas and Functions

Discussion List

Help Article Resources