Want to practice working with formulas directly in Smartsheet?
Check out the
Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
Product questions?
Ask it here! The community's got your back.
Discussion List
Lock Vlookup
Hello, With the below vlookup, I'm pulling new employee's trainers. However sometimes the trainer changes midway through the class, is there a way to lock in the first trainer that is pulled? =VLOOKUP([Employee ID]@row, {New Hire Trainer}, 10, false)
Stoplight based on date and status
Hi Community! I'm at a hard stop on stoplights (pun intended). I am looking to populate stoplights on a sheet based on days to due date and status of a line. Columns on sheet below, with the "At risk?" being where I'd like my stoplights: I would like to populate a green stoplight for the following conditions: -If today's…
COUNTIFS formula question combining date condition and text condition
Why does the first formula fail when it is a combination of the bottom 2? The column is a Text/Number column Asia formula: =COUNTIFS({CSA UK Surveillance Region}, =[Primary Column]@row, {CSA UK Surveillance Db. Expiry Date}, <TODAY(+90)) Europe formula: =COUNTIFS({CSA UK Surveillance Db. Expiry Date}, <TODAY(+90)) North…
Populating a checkbox based on the value from another worksheet
I have a checkbox column on one worksheet that I would like checked based on the data from another sheet - basically changed to "Approved". The first sheet is named Project Documentation Tracker and the second sheet is named Approved Selections. It would be based on Approved Selections Column Category = Project…
Suggestions for changes to smartsheet
Hi, I am not able to find the link to suggest changes to smartsheets. Is there one? We mark items where there is an automation to send to an email. If we mark different rows without waiting several minutes in between, smartsheet sends them combined and in a horizontal format. This is time consuming because the horizontal…
% of checked off tasks by task category
Hello! I'm attempting embed a tracker for understanding progress completion by different categories within a project plan. See screenshot below! This formula is supposed to count if a task is checked off within the hub and spoke category / total number of items (checked or checked) within that category to give me a percent…
Is there a way to pre-populate past survey responses by user?
I have a feedback survey(form) that users fill out a few times a week and I was wondering if there is a way to have their responses pre-load from the last time they answered. For example, say they fill out the survey three times a week and nothing has changed over the course of the week and they want to submit the same…
Symbol Formula including 2 Columns?
Hi All! I'm working on a Smartsheet and I need a logic formula to update the symbol color. I have 2 columns that I am using in the formula [2 Signer - Status] [1 Signer - Status]. When the status in either of these cells is "Sent" "Delivered" or "Draft", I would like the color of the symbol to be "Yellow". When the status…
COUNTIF with filtered data or collapsed levels
Hello, Is there a way to do a =COUNTIF with a sheet that looks like this? The columns aren't filtered, but I do have several collapsed levels. I tried holding the Ctrl cell and selecting cells that way but it doesn't work... it's not ideal either, since some levels could be extensive.
Automatic generation of form from template
I have a form for new clients to submit company self-onboarding info. I would like each new company submission to generate a new form from a template to collect user info for that company. The goal is to have the users for each client to be collected in an individual sheet rather than having one sheet with users for all…
Help Article Resources
Trending in Formulas and Functions
Need to find all instances of value and return any non-blank cell from another sheet?
Hello Everyone, I'm stumped and need some assistance. What I am trying to do is look for the value in the Cable Number column (Cable Number]@row) on the master sheet in the Cable Number Column on the Update Tracker (Update Tracker Cable}). There will be multiple times this value is in this column but some dates will be…
Hello I am trying to create a formula that returns a specific date when a dropdown selection is made
class requested start date Jan 13, 2025 - July 14, 2025 Oconomowoc, WI Jan 13, 2025 - July 11, 2025 Huntsville, AL (new site) Feb 10, 2025 - Aug 11, 2025 Amsterdam, NY Mar 24, 2025 - Sept 22, 2025 Fontana, CA Apr 21, 2025 - Oct 20, 2025 Oconomowoc, WI May 13, 2025 - Nov 17, 2025 Huntsville, AL Jun 16, 2025 - Dec 15, 2025…
How Do I add Sum(Children) at the bottom of the children rows
Hi, How do I use the =Sum(Children) function in Smartsheet to sum child rows and display the total at the bottom. I usually create a parent row, then add rows beneath it, indent them and add the formula =Sum(Children) to the parent row to calculate the total. However, that structure was confusing for users, and they…