Formulas

 

Discussion List

  • Returning matching value in subset of rows

    I have a sheet with groups of task types that need to be completed for multiple units. The tasks happen in a sequence (always). I am trying to extract the highest Task Name completed for each task type + unit combo to ultimately display it in a dashboard. So for example, the Highest Completed column for the combo of the…

  • Update a Smartsheet row date automatically when a Google Calendar event is moved

    Hi all, I'm trying to link Google Calendar events to specific rows in a sheet so that when an event is rescheduled in Google Calendar, the date on the matching Smartsheet row updates automatically. From what I can see, the native integration doesn't cover this. Publishing a sheet to Google Calendar is one way (Smartsheet…

  • Formula for a metric count from a column with multi-value cells

    Good day! I am trying to create a formula to metric count instances of an issue phrase. The column allows for multiple issues to be selected by users. There are two other parameters for the metric, each is a single-value column. The formula I am using is: =COUNTIFS({HOPE correction /SFV tally Org}, Org@row, {HOPE…

  • "HAS" Function Typo

    The summary provided for the "HAS" function contains a typo: "Search for an exact match of a value or values in within a range. Returns true if found, false if not found" This literally does not matter at all I just thought it would be funny to point it out.

  • How do you spread activity hours across weeks for a resource forecast?

    Hi all — hoping someone has solved this before I go and reinvent it badly. What I have One row per project activity in a sheet, with columns for: • Developer hours • Design hours • PM hours • Service type/Milestone • Start date • End date What I want A weekly resource forecast — week commencing across the columns, service…

  • Entering a number when a word is chosen

    I have two columns (Incident or Accident) and (Points Received). When Incident is picked from the dropdown I need "1" entered into Points Received. When accident is picked from the dropdown I need "2" entered into Points Received. This is what I have but it is not working: =IF([Incident or Accident]@row = "Incident", "1",…

  • Best approach to migrate an Excel project plan with 600+ tasks and dependencies to Smartsheet

    Hi Community Members, I’m planning to migrate a project plan from Excel to Smartsheet. The sheet currently contains 600+ tasks, and many of the tasks are connected using predecessor/successor dependencies. I would like to clean up the Excel file before importing it into Smartsheet by removing around 200 completed tasks.…

  • Formula help needed

    I am trying to extract information from the main sheet and create a report that calls out Garment Type in the date range of our FY.

  • FORMULA HELP Collect number from other sheet, but with different data points to check before collecting

    Here's what I thought the formula would look like: =INDEX(COLLECT({PM Budget 2026 Range 1}, {PM Budget 2026 Range 2}, [Tactic / Category]@row, {PM Budget 2026 Range 1}, >0), 1) AND({PM Budget 2026 Range 3}, {PM Budget 2026 Range 3}, >=[Start Date]@row, {PM Budget 2026 Range 3}, <=[End Date]@row)) It comes back…

  • Duplicate Notifications From Automations

    We are getting duplicate notifications from our automations. These automations have been in place for over three years and have never done this before. There have been no changes made to any of them. This is happening on a variety of sheets and a variety of automations. It started sometime about mid August. Is anyone else…