Formulas and Functions

Archives for May 2018

RYGB X Checkbox question
Hello again Community! I currently use the following formula for our projects: (Works well!) =IF(Finish15 <> 1; IF(due_date15 - planned_date15 > 0; "Red"; IF(due_date15 - planned_date15 > -3; "Yellow"; "Green"))) But now I would like to include a blue ball when the "finish" checkbox is checked. I tried some alternatives,…
Invalid data type/unparsable for date countdown
I'm trying to have an automatic countdown but I keep running into an invalid data type or unparsable error. I am trying to show how many days remain until a point in the future (07/17/2018). Here are some of the formulas I've tried without success: =NETWORKDAYS([timer start]1, [Minimum billing date]1) =[Minimum billing…
Column Number Function
Hi is there a function that can return the column number when given the column name? This would help when columns are added in sheets that have vlookups referencing them.
Display last value in column
Hello Community! My question is quite simple. (I hope). How can I display the last value of a column? I tried to use the formula = INDEX, but I could not read the last number automatically. Thanks!
Nested IF Statement Formula
Hello Team, This would be a great formula to incorporate into my daily SmartSheets process within my organization. I would like to know how to create a nested IF statement if this is the correct formula to use, that uses an RYG (red, yellow, green) light flag formula to set the status of a task based on its…
Enhancement Request: Sheet/Workspace Level References in Formulas
I would like to create notifications for the creation of new sheets in specific workspaces and in general better track what sheets are getting created where. To this point I would like to build a master sheet with drop downs for each workspace showing what folders and sheets are in them. A directory if you will. This would…
How to reference the <CELL ABOVE>
Is there a way to reference the cell above? example: =IF(logical_expression, value_if_true, <CELL ABOVE>) The simple way is to reference using [COLUMN_NAME]2 (the number being the current row number minus one). But this is broken anytime a row is deleted, which happens frequently on our sheet. I have tried adding an…
count if check box is not checked (2) and requested date is within a date range
I am trying to get a count of each row on my sheet that meets the following criteria. Children of ([Requested Date]17) (which is date format column) is between 1/1/18 - 1/31/18 and Children of (Canceled17) (which is check box format) is not checked (0) and Children of ([Not Owner]17) (which is a check box format) is not…
Average of multiple rows
Hi - I am running into a problem with finding the average of multiple rows based on the two previous columns. I want to return the average of all the rows in column Fall Rate that correspond with Month = 1 AND Year = 2018. This would then populate the 2017 and 2018 columns by Month on the other sheet. Any ideas would be…
Dynamic lookup for Project Master Rollup
I would like to create a master project rollup to track projects, and do not want to set up cell linking every time a new project is added. To this end can we create a dynamic lookup so all the user has to do is type the name of the sheet referencing the new project into the master rollup?

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 for return value from a seperate sheet column if another column is one of 3 values
Hello, I am trying to create a formula that returns the value of a lets call it Column A in a seperate sheet if Column B in that same seperate sheet is any of 3 different words. Here is the formula as I currently have it. =JOIN(COLLECT({Scopes Range 3}, {Scopes Range 2}, OR(@cell = "In Progress - On Time", @cell = "In…
If Formula help
Hello, I am trying to write an IF formula for my smartsheet that adds the letter A, B, C or D or E to a column pending on a dollar amount listed in another column, but I cannot seem to get the ranges to work right and everything is listed as either A or B. Current base formula: =IF([Total Assets]@row >= 1000000, "A",…
Automate formula based on dropdown
So I have a sheet with multiple columns. Rows automatically appear based upon a Power Automate flow from other sources and rules. It works great, but now I need to auto populate a calculated formula in one column based on a drop down selection in another. The two things I thought of don’t seem to be working. Example: I…

Formulas and Functions

Discussion List

Help Article Resources