Formulas and Functions

Archives for August 2023

Incorrect Argument Set when using HAS in an INDEX(COLLECT
Has anyone had much success using a HAS in an INDEX COLLECT? I'm trying to get something like this to work: =INDEX(COLLECT({ITEM ID}, {TONER COLOR}, [Toner Color]@row, HAS({MODEL}, [Xerox Model]@row), 1)) In one sheet I have the following columns: ITEM ID TONER COLOR MODEL The models have multi value selects. Look at this:…
Uncheck a Box if a cell is blank
Hi! New to smartsheet so bear with me. We have a smartsheet with a lot of users and we want to have certain columns revert to unchecked at the row (they are check box columns) if our "Supplier" column is blank. One thing though is our supplier column reverts to yellow background fill when its blank, so I hope that wont…
What formula can i use to get the count of items that the due date is greater than the current date?
I tried this but got back #unparseable.
INDEX/MATCH/ISBLANK
Hello, I'm having trouble with a formula. I'm looking to return a cell value from an INDEX/MATCH function but if it's blank, to return another cell value from an INDEX/MATCH function. Company adds travel justification based on employee ID's. If the employee ID hasn't been generated yet (pre hire travel), then the manager's…
Using CONTAINS in INDEX/COLLECT cross-sheet formula
Can I use the CONTAINS function within an INDEX/COLLECT cross-sheet formula? I need to pull the Finish dates from a schedule for Tasks that contain a designated text string. There will be multiple tasks that contain that text string. Here is the formula that I have used so far, but I am getting an #INCORRECT ARGUMENT SET…
How to exclude a column formula on certain rows in a specific status
I am trying to capture when requests are on time vs past due in the 'Current Status' Column, and also trying to capture how many days since a request was submitted in the 'Days since Req Created'. I have those formulas figured out. However, I don't want those to run when the 'progress' is completed. So how do I add that in…
Nested IF ANDs and ORs to enter in a date for check in and out
I am having difficulty writing a formula to enter in a date for check in based on the days that staff will be at a conference. I have check boxes for the days that people will be at the conference and I wanted to have the check in/out dates autopopulate based on that information. I am able to get the first couple columns…
How can I create a formula to count occurrences with THREE different value criteria.
I am trying to create a formula that will pull a count of occurrences with 3 specific values. I currently have a main sheet that is capturing a Child Care Center's student spaces by age and if the space is "Filled or Vacant." Screen capture of master sheet to source data for metrics sheet: I then created a metrics sheet to…
Milestone met, change end date
I have a task that a team member has a month to complete. Instead of the team member needing to update the Date Finished column, I would like them to be able to mark the milestone as complete and have the milestone start/finish update to [Today] which would trigger the end date of the Task above match the milestone date.…
Matching Neighbor Cells or Rows to Most Recent Date
Hey All, Background: Im trying to build a fluid dashboard that tracks information coming in from a form. This form is then routed to several sheets where I break it down further based on certain criteria. I am looking for a way to report on the most recent metrics in the given sheet using the sheet summary feature to make…

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

Formula Help - if cell = X then show network days
I hope my explanation makes sense. I currently have a basic formula that shows "networkdays" between 2 dates, however it obviously shows "invalid date type" when the "end date" has not been entered =NETWORKDAYS([Date Open]@row, [Actual Closure Date]@row) . So we want the cell to show "duration to close" when status is…
Count Duplicates Only if the Field is NOT Blank
I am struggling to get the formula right to solve this problem. My formula can count if there are duplicates in this field, but it also counts it if the field is blank (because many rows won't have a value in this field). Can anyone help me add the "if it is not blank" piece so i only get a 1 if there is a value in the…
Issues with Convert to Column Formula
Hi, I have a column that I'm trying to convert to column formula but won't because "The column formula syntax isn't quite right" and gives me Smartsheet's help article for more information. The column's type that I'm trying convert is "Symbols" and is the Progress Bar. The Formula of the attached example is: =IF([RPM %]851…

Formulas and Functions

Discussion List

Help Article Resources