Formulas and Functions

Archives for September 2023

Automating Copying/Updating NON-BLANK Cells From One Sheet to Another Using a Unique ID Cell
Hey Everyone! I'm trying to set up a form of automation that's quite a bit more advanced than anything I've done in Smartsheets before and can't seem to find a solution. I'm admittedly pretty new to the advanced functions of Smartsheets. In this scenario, I am using a form to evaluate skills of field engineers. The…
Looking to Resolve #NO MATCH with a Multi-select Dropdown
I've tried many variations and got very close with: =IF(CONTAINS("Orientation", Course@row), INDEX({Course Start Date}, MATCH(Course@row, {Courses Dropdown Name}, 0)), "") - it only pulls when there is one selection provided in the multi-select dropdown and a "#NO MATCH" where there are more than one course selected. We…
Automated DOTs byt date, Need Checkbox TRUE to equeal Green Dot
So, I am workng on a invoice tracker that currently is using RGY dots for past due invoices by date. I need to alos formulate that if checkbox 9 is true then status"Green" =IF([Payment Due Date]7 < TODAY(), "Red", IF([Payment Due Date]7 > TODAY(), "Yellow", IF([Payment Due Date]7 = TODAY(), "Green"))) This will change the…
If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…
IF cell is Blank
Hi I have formula here: IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status}; {Truck Dispatches (Ongoing Jobs)_Truck}; [Truck reg. number]@row); UNICHAR(10)); 1) This formula looks at the sheet {Truck Dispatches (Ongoing Jobs)_Status} and returns the Status, by comparing the Truck reg. number from two…
How to write a formula that avoids dividing by zero
I am trying to write a formula to get an average for total absence, no shows and regrets; however some cells are zero which returns an error or unparseable message. Below is the formula I've written that does not work. The problem is with dividing by the [Total Absent] because sometimes it is zero. If it is zero, I want it…
#INVALID DATA TYPE SUMIFS function
I'm struggling to rid of my #INVALID DATA TYPE error in my function. I understand that the error means the function is trying to act on a data type that is incompatible with some part of the function. However, I have thoroughly looked through the sheet, and the sheet it is referring to, to validate the data types. Here is…
Is there an automation to move an attachment to a new sheet?
I have a sheet that contains attachments tied to client rows. I'm looking for a way to archive the attachments to a new sheet without needing to go into each row and do this manually. Does anyone know if there is an automation that will move attachments? Thank you!
Formatting columns imported from Excel
I made a Smartsheet off of an Excel document. Several columns are marked X in a text format to indicate YES to a question. I'd like to use a checkbox instead. I created a column that has checkboxes next to the first of these types of columns. How can I create a formula that is if column A has "X", then check the box in…
Dashboard Last Updated Date
I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Record a date automation isn't working
I set up the automation below, and it's not working. When I change the status of a row to complete, nothing happens. If I go into the automation manager and select "run" or "test" the completion date column fills in with today's date. What am I doing wrong? This should be straight-forward. I've tried building this…
Identifying oldest entry on sheet, for different groups
Hello! I am working on a "waitlist" process, where the sheet will have multiple candidates under each work location. I need a formula to identify the oldest entry for each location, and if there are multiple entries for the earliest date, use a "score" column identify the higher score as the next candidate in line. Example…
Week Over Week Change
I have a sheet that has orders entries from the last 6 months. This sheet continues to populate as more orders are made. This is just my repository sheet. I am looking to get the week over week change metrics for the last 7 days. Here's the formula that can provide me that week over week change: Week over week %% =…

Formulas and Functions

Discussion List

Help Article Resources