FAQs: Using formulas

Yes. A formula can reference a single cell, range of cells, or full columns in another sheet with cross-sheet formulas. 

We do! However Smartsheet has an INDEX and MATCH combination that is more versatile. This combination reduces cross-sheet cell references by looking at columns individually instead of using one large range.

Take a look at our article on formula combinations for cross sheet references for an example.

You can still learn about Smartsheet's VLOOKUP function in this VLOOKUP article as well, or find examples of how other Smartsheet customers gather data using formulas in the Smartsheet online Community.

If a new row is inserted above, below, or between two rows that include the same formula, the new row will inherit that formula automatically.

If you're creating a formula to use all values within a column, exclude the row references and only use the column name in the included range (e.g. =SUM([Total Hours]:[Total Hours]). This ensures that any added rows will be automatically included in the calculation.

If you've set up a column formula, the formula will automatically apply to every new row that gets added to the column.

Yes. The character added in the formula depends on the symbols you use. Refer to this list for what to add in the formula:

• Star, flag, and checkbox symbols: For these symbols, use 1 for on or 0 for off
  • Example: = IF([Status]@row = "Complete",1,0)
• Red, yellow, and green light symbols: Use the literal values Red, Yellow, or Green
  • Example: =IF([Status]@row = "At Risk", “Red”, "Green")
• Priority symbols: Use High or Low
  • Example: =IF([Status]@row = "Overdue", "High", "Low")
• Harvey Balls: Use Quarter, Half, Three Quarter, Full, or Empty
  • Example: =IF([% Complete]@row = 1, "Full", "Empty")

These are three likely reasons why it’s appearing as text in a cell:

• It’s missing the equal sign (=): Formulas must always start with = 
• Dependencies are enabled in the sheet: When dependencies are enabled on a sheet, formulas aren't allowed in these dependency-enabled columns
  • Start 
  • Duration 
  • Finish 
  • Predecessors 
  • % Complete or % Allocation: These columns automatically generate values based on the dependency settings and overwrite formulas. If you type a formula in one of these columns, it’ll be displayed as plain text rather than performing a calculation.
• You created the formula from a report or a mobile device: We don't currently support creating formulas from reports or from the Android and iOS apps. Any formula you enter from reports or from a mobile app will display as plain text rather than calculate or find values.

Cells in sheets or reports use the decimal value, not the percentage format. This is true even for columns using percent-formatted values. 

To compare percent values or reference percent columns, refer to the table below.

When dependencies are enabled, columns such as Start Date, End Date, Duration, and % Complete have inherent formulas to provide project functionality. Formulas aren't allowed in these columns, as they may be overwritten. 

To work around this behavior, you can either disable dependencies on the sheet or create new columns to perform the desired calculations.

When dependencies are enabled on a sheet, the Start Date and End Date columns have an implicit time component. The time isn't displayed in the column, but it can affect results of formulas. 

For example, you have a formula that subtracts TODAY() from the start date—which is today. You would generally expect a result of 0. What you'd actually get is a decimal value, as Smartsheet tries to perform the subtraction based on the implicit time stamp. To work around this issue, use the DATEONLY function. 

To go along with the example, the formula =[Start Date]2 - TODAY() would be revised to =DATEONLY([Start Date]2) - TODAY().

In this case, Smartsheet only looks at the date, not the time when performing the calculation.
 

Use the DATEONLY function. Dates in System columns are a display value and not the actual value. System columns record UTC dates, but they display your local date/time based on the UTC date. You may see results you don’t expect if you don’t use DATEONLY function. 

When you use DATEONLY, it extracts the date from the System column. Here’s a sample formula: =NETWORKDAYS(DATEONLY(Created@row), TODAY()).
 

Opening reports and dashboards that reference a sheet with a formula using the TODAY function will not update the TODAY function to the current date. There must be an update to the underlying sheet itself.

 
You can see a list of actions that will update the formula in the TODAY Function article.

To automatically update your underlying sheet without opening it, take a look at the Automatically update the TODAY function in formulas article or use Bridge to Schedule Daily Sheet Save.
 

Formulas won't be preserved when you use these commands to move or copy rows. You can, however, use keyboard shortcuts to copy and paste formulas.

Often, this calculation error occurs because one of the referenced values is being considered as a text value. 

To correct this error, check that neither of the values being referenced has a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe before it, this undesired result occurs.

A trick to catch this is to check the numeric values formatting alignment. Often, values being considered as a text value appear left aligned in the cell, instead of the normal right alignment for numeric values.

Numeric values appear left aligned when they’re: 

• Produced by formulas, and  
• In the Primary column. 

However, these values will still be recognized as numeric values. Unless something in the formula or cell dictates otherwise. 

Another option to correct this error is to use the VALUE() function. The VALUE() function converts text strings containing numeric values to numbers.

If you’re evaluating the same range of data for multiple criteria, use the OR Function and @cell parameter to have the correct value returned.

If you are evaluating the same range of data for multiple criteria, use the OR Function and @cell parameter to have the correct value returned.

Yes. If the cells being referenced by the formula are text values and don’t create an email address, they’ll return as only a text value. 

Functions that can return a contact value work in contact columns. Here are some sample functions you can use:

• Equals Contact cell: =[Assigned To]1
• VLOOKUP: =VLOOKUP([Assigned To]1,{Departments},2,false)
• PARENT: =PARENT()
• INDEX(MATCH()): =INDEX({Departments},MATCH([Assigned To]1,{Names},0))
• IF: =IF(Urgency1="High",[Urgent on call]1,[Assigned To]1)

Below are some specific examples of these formulas in action.

Sample sheet information:

Given the table above, here are some examples of using Contact Formulas in a sheet:

Currently, we don’t have a direct method to create new contacts with formulas. You can create  new contacts using multiple columns if the email addresses of the org follow a particular pattern. For example, first.last@domain.com.

To do this, you can have a column for first.last and another for @domain.com. Using the JOIN function can bring these two fields together into the same contact cell, and create the desired contact.

Formulas that output data types other than contacts or text (e.g. dates, numbers, booleans, etc.) aren’t supported and will return an error code: #CONTACT EXPECTED. This is similar to how DATE columns work today. Formulas calculate to #DATE EXPECTED, if they output anything other than dates or text. Checkbox columns have a similar error: #BOOLEAN EXPECTED.