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

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Can you match a date from a drop down column?
Hi, I was trying to focus on preventing errors by added a list of very specific dates one can submit requests. I was hoping to have a drop down of dates to choose from. Then I have additional columns that will match that date selected and autofill the rest of the due dates from another sheet. If the user inputs the date…
Compare Previous Week
Hello, I am trying to be able to say whether hours went up or down as compared to the previous week but am having trouble referencing the previous week. I have tried to add a helper column but am still getting an error. Any help would be greatly appreciated!

Formulas and Functions

Discussion List

Help Article Resources