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
Q. Can a formula reference a cell in a different sheet?
A. At this time, Smartsheet formulas can reference cells only on the sheet in which the formula exists. You can create cell links to get values over to another sheet, then create formulas on those values. See Cell Linking for more information.
Q. Does Smartsheet support a VLOOKUP() or other lookup functions?
A. While Smartsheet does not currently have a function called VLOOKUP(), it does include LOOKUP() and MATCH() functions. These functions can be used to reference items from the sheet in which the formula occurs. For more information, see the Using Formulas article.
Q. How can I get a formula to include rows that are newly added?
A. If a new row is inserted above, below, or in 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.
Q. Can columns with a symbol column type be controlled by formulas?
A. 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)
Check out our Support Tip in the Smartsheet Blog:How to Use the Checkbox Formula
Red, Yellow, Green Light
Use the literal values “Red”, “Yellow”, or “Green”, for example:
=IF([Status]5 = "At Risk", “Red”, “Green”))
Check out our Support Tip in the Smartsheet Blog:How to Automate RYG Balls
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") )
Q. I typed my formula correctly but it's displaying as text in the cell. What went wrong?
A. 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.
Q. Why isn’t my formula referencing % Complete working?
A. 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”, “ “)
Q. Can I create formulas in project sheet columns?
A. 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.
Q. My formula referencing the Start/End Date column isn't working. How can I fix it?
A. 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.
Q. 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?
A. 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.