-
Health of Work/Color Formula Question
Hello, I am new to Smartsheet and have no idea on how to properly articulate the following rules in a formula. I am using the colors RYGB and following statuses: • New = green, if end date is one week away yellow, If end date has passed red • In progress = if % complete is between 80-90% and not overdue green, otherwise…
-
Cross-sheet COUNTIF Formula w/in a specific date range
Hi all, Looking for some help on a formula I have been working on. I am trying to count the number of requests that have been submitted between a specific timeframe (2/14/22-3/18/22). The date column I am looking at is in another sheet (Date OnlyN). =COUNTIFS({Date OnlyN}, >=DATE(2022, 02, 14), {Date OnlyN}, <=DATE(2022,…
-
Can IF function be used with symbols and cross-sheet referencing?
I'm trying to create a formula that shows the Green and Red symbols based on information from a different sheet. Current formula looks like this: =IF({Open House Order Form Range 1} = Office@row, "Green", "Red"). I get an invalid operation error but I can't figure out why. I'm wanting to know if any cell in the cross-sheet…
-
COUNTIF with date range and another critieria
Hi, I'm trying to count the number of rows which have a 60-day opt-out window between x and y and where the "Monthly DataCor Revenue" is blank. This formula works (y'all helped me with this): =COUNTIFS({Lite 60 day opt out}, AND(@cell >= DATE(2022, 8, 1), @cell <= DATE(2022, 8, 31))) but I cannot get an answer when I add…
-
Harvey Ball formula based on dates
Hello, I have Revision Due column and I would like to automate the Harvey Ball formula that if the Revision Due date is older than today's date the ball would be Red. If the Revision Due date is within 60 days of today the ball would be yellow. If the Revision Due date is days the ball would be green. I've tried the…
-
Bypass Circular Reference
I've gotten myself into a bit of a pickle here and I see no way out, I have three columns: Primary, Level, and ParentID. The Level is the number of ancestors that a row has. The ParentID is an identifier of which Level 1 row this row is descended from. ParentID has a formula which is: IF(Level@row = 0, "", IF(Level@row =…
-
How to append sheets/consolidate multiple sheets in one sheet
Hi All, here are two different sheets populated from two different forms which are site specific The forms have information for people to read which are site specific hence why two forms Is there a way to append these two sheets into one like you can do on power bi using the append query function? please can you advise
-
If column does not contain string return N/A
Hi, I've created a formula to return me the average "word" that appears when the Title column contains the string "1. Establish Baseline for Web Performance": =IF(AND(COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "High-3",…
-
Does Data Shuttle's Upload Data work in a sheet that has column formulas?
Hello! I've been having successful experience using Data Shuttle Upload Data with sheets that are referenced in another sheet. Now I have a sheet (sheet name: Tracking) with multiple column formulas, Data Shuttle Upload Data work doesn't seem to work... When I try to set up the workflow, the columns that are formula driven…
-
Formula Help Needed to Remove 0 From Results
Hello Smartsheet Community! I need some help to better understand what is happening. Why are only some of my results generating a leading 0 in front of my text? I have a multi-select column with a variety of text values for more than 20 groups. In the same sheet, but in different columns, I have written out a formula to…