-
range checking & flags
Hi, I'm trying to leverage a formula to check if the employee ID is listed on another sheet. Sheet 1 (sanity check): This is where I want the formula to live. On sheet 1, if the employee ID on the row is not listed anywhere on Sheet 2's Employee ID column (range), then turn the flag on. If the employee ID on the row is…
-
Flagging rows based on number of occurrences within a range of another date
I have a sheet where I want to count the number of occurrences of the same [PI Name] within a 30-day period [Sx Date]. I only want to count each PI once on any given date. I've given an example below to show how I want the counting done, but the solution can look different from this. Whatever solution I use will have to be…
-
Collect from another sheet
I want to populate a date in sheet A based on the min date in a list of dates on sheet B. What is wrong with this formula: =MIN(COLLECT({REWORK Sprint Dates Range 1}, {REWORK Sprint Dates Range 1}, >=[Start Date]@row ))
-
Trim Leading/Trailing Spaces
This is pretty basic but I'm not sure what formula to use in Smartsheet to trim any leading / trailing spaces from values that are entered into cells. In excel this is a simple TRIM formula but there doesn't seem to be an equivalent in Smartsheet. I've tried the formula glossary and AI but unfortunately I'm not finding the…
-
Budget - Sum total of a specific category
Hi! I'm working on a simple budget sheet and I'm having difficulty with a formula to sum total a specific expense category. I want to sum all expenses in a given category from another sheet. This is what I have so far but it's pulling back $0.00 despite their being items that match the criteria. =SUMIFS({Expenses Range 2},…
-
Automation to Move cells without individual logic
Hi Smartsheet Community, I’m looking for assistance with optimizing our workflow for our project intake process. Here’s our current setup: All project requests come in via a single intake form. They populate into our Prioritization Tracker Rows are triaged as Declined (move to Declined sheet), Backlog (move to Backlog…
-
Help with a Simple Formula
I am trying to calculate a formula that I can use in a dashboard graph. I need to show the following: status of a task (i.e. completed, in progress, etc) That it is US only (management responsibility) The end date is within the year 2026 This is what has been generated for me, through SS AI - COUNTIFS({My Sheet Name here…
-
Getting error in a countif formula
Hi all, I am trying to expand on my regular countif formula but I am getting an error. The original formula: =IF(Order@row = 2, SUMIFS({Costs}, {Date}, Date@row, {Location}, location@row), SUM(CHILDREN())) works perfectly fine. However, when I add onto this formula, I get an error: =IF(Order@row = 2, SUMIFS({Costs},…
-
INDEX/MATCH formula error, "No match" when there is an exact match
Hello! I'm using an INDEX/MATCH formula to return the "Type" associated with a specific "Email." I'm getting a no match error when an Email value is first repeated. After the first repeat, the formula no longer functions. I've included my actual and desired outputs below. Both the form submissions data and the summary data…
-
Is it possible to reference a cell or summary field in a column formula?
At the top of our templates we have a Summary section where project (deliverable) details and key people are specified. I am wanting to reference the 'Deliverable Sponsor' cell within a hidden column on the sheet so we can create reports pulling through all row data across all deliverables that belong to that Sponsor. From…