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
Collect most recent date from another sheet based on certain criteria
Hello I have a fleet maintenance log that has invoices per vehicle with a column that is a drop-down with multiple values based on the service provided. I am trying to make a sheet that would be a quick reference to see the most recent service per vehicle per category. Being multiple services will but under a category I…
How to count a range of different names from a range of dates?
Sheet A has a week ending date column (always Sunday). Sheet A also has a column called Manpower where I am trying to write a formula. Looks like this, Sheet B Has an install date and an Installers Name. Looks like this, The formula I am trying to write would find all dates from Sheet B that fall within the week ending…
#Invalid Ref for no reason
Smartsheet is garbage. Getting invalid ref errors despite doing things EXACTLY the same way on cross sheet references as I had elsewhere in the same sheet. Underlined section throwing the error. Other two are just fine. Just a different set of columns. WTF
TODAY function and status
I'm working a formula for a RYG column based on two major sets of data: Status and Quarter Start Status and Quarter End Q Start and Q End are the dates of the quarter and they are on the sheet as helper columns. For example: If Quarter starts in the next 80-120 days: blank status - Yellow Status is Product discovery,…
Exclude a range of values from a source range of values
Hey all, I've got Sheet 1 - List 1 that is an intake sheet with a range of opportunities (each identified by a unique opportunity number with a set number of digits) exported from our CRM program. This sheet is automatically updated whenever a seller puts a new opportunity into the CRM. I am not allowed to make changes to…
Dates - Due, Complete and Overdue based on Completion Date
I have a due date and completion date columns in my Smart Sheet and need help with formula to give responses based on dates. I have attached example for reference. For column with completion date being blank, would like to show "Due", for completion date before due date to show "Complete" and completion date past due date…
IF formula to compare due dates to show completion schedule
Hello all! I have a formula that is stumping me - I am looking to compare a target due date versus the actual completion date. I have a formula that is partially working, but am running into issues when I use the term "ongoing" on the target due date column (see Project 4 in the below example). This is what I'm trying to…
Overall Text Rating from a Numeric Rating/Score
Hello! I need help. This is probably basic, so forgive me, but I want the overall rating to show as the text rating assigned. For instance, 1–1.49 would be below average, 1.50–2.49 would be average, and 2.50–3 would be above average. I have messed with this for over a week and I can't figure it out. Thanks!
Check a Checkbox in one Sheet based on Checkbox Checked in another Sheet
I am trying to make checkboxes check in a multiple entry sheet when a checkbox in a core data sheet is checked. The core data sheet holds employee information that is referenced in the multiple entry sheet. When the employee leaves I have a checkbox to check in a column 'Employee left business' and this column is…
indexmatch error
I have tried the formula in office and it works fine, but not in smartsheet, why is that? It doesn't show no match or any other error, but it doesn't show the correct result!!!!!!! 😥help me please!
Help Article Resources
Trending in Formulas and Functions