Formulas in Smartsheet can save you time by calculating data automatically, by combining cell contents in a sheet, or by helping you keep track of dates (to name just a few examples). Using formulas does take a bit of practice and know-how. To help you get up to speed, this article is a compilation of answers to common questions (FAQs) that customers often have as they’re ramping up.
For specific questions about formulas, we also recommend the Smartsheet Community. The community is full of helpful, knowledgeable people with practical Smartsheet experience asking and answering questions — who knows, someone else may have already solved the problem you’re working on.
Answers to Questions About Formulas in Smartsheet
Can a formula reference a cell in a different sheet?
Yes, a formula can reference a single cell, range of cells, or full columns in another sheet, using cross-sheet formulas. Learn more about this in our article Formulas: Reference Data from Other Sheets.
Does Smartsheet support a VLOOKUP function?
We do! You can find out more about this in the VLOOKUP Function article.
How can I get a formula to include rows that are newly added?
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 (learn more about this in our article on Auto-filling Formulas and Formatting).
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 will ensure that any added rows will be automatically included in the calculation.
Can columns with a symbol column type be controlled by formulas?
Yes, each of the symbols can be set using values in a formula.
Clicking the drop-down arrow in the cell containing the symbols will show you the spelling and capitalization to use to reference the values in a formula. Here are some examples:
Star, Flag, and Checkbox
For these column types, the symbol is either on or off. You can use a 1 for on or 0 for off. For example, you can place this formula in a checkbox column to create a checked checkmark when the status is Complete, use a formula similar to the following:
=IF([Status]5 = "Complete", 1, 0)
Red, Yellow, Green Light
Use the literal values “Red”, “Yellow”, or “Green”, for example:
=IF([Status]5 = "At Risk", “Red”, “Green”))
These columns use the value of “High” or “Low”, for example:
=IF([Status]5 = "Overdue", "High", "Low"))
These symbols show quarters of a circle by using the values of "Quarter", "Half", "Three Quarter", "Full", or "Empty", for example:
=IF([% Complete]5 = 1, "Full", "Empty") )
I typed my formula correctly but it's displaying as text in the cell. What went wrong?
The three most likely reasons for text appearing in a cell rather the formula you expect are:
Formulas must always start with = (an equal sign)
Dependencies are enabled in the sheet
When dependencies are enabled on a sheet, formulas aren't allowed in the dependency-enabled columns: Start, Duration, Finish, Predecessors, % Complete or % Allocation.
The values in these columns are generated automatically based on the dependency settings and would overwrite formulas. If a formula is manually typed into one of these columns, it will 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 or iOS apps. Any formula entered from reports or from a mobile app will display as plain text rather than performing a calculation.
Why isn’t my formula referencing % Complete working?
In columns that are formatted using the % button on the toolbar, if you turn off the formatting you will find that though a value might display as, say 50%, the actual value is expressed as the decimal value of 0.5. In your formula (and also in reporting criteria and conditional formatting), use the decimal value for the percentage to properly compare % formatted values. For example: =IF([% Complete]5 < .25, “At Risk”, “ “)
Can I create formulas in project sheet columns?
When dependencies are enabled, columns such as Start Date, End Date, Duration, Allocation % 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.
My formula referencing the Start/End Date column isn't working. How can I fix it?
When dependencies are enabled on a sheet, the Start Date and End Date columns have an implicit time component. Though the time isn't displayed in the column, it can impact results of formulas. For example, let's say you have a formula that subtracts TODAY() from the Start Date, and your Start Date is today. You would generally expect a result of 0, but 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 will only look at the date (and not the time) when performing the calculation.
What happens to my formulas if I use the "Move to Another Sheet" or "Copy to Another Sheet" commands to move or copy a row containing a formula to another sheet?
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. For more information, see the “To copy and paste formulas” section of the “Using Copy & Paste” help article.
Why are my numeric values not calculating correctly? For example, when adding 1 + 12 it’s producing the result 112, instead of 13.
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 contains a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe in front of it, this undesired result would occur.
A quick way to identify numeric values being considered as text values is to check the numeric values formatting alignment. Often values that are being considered as a text value will appear left-aligned in the cell, instead of the normal right alignment for numeric values.
Note: Numeric values that are produced by formulas will also present as left-aligned. However, these values will still be considered and recognized as numeric values. Unless something in the formula dictates otherwise.
Numeric values will also appear automatically left-aligned in the Primary Column specifically. The Primary Column can be easily identified because it is the only column header that is formatted BOLD. Numeric values in the Primary Column will still be considered and recognized as numeric values unless something in the cell dictates otherwise.