-
How to create a source sheet that automatically updates other sheets if data is removed?
Our organization has roughly 150 program participants and we have a number of different sheets that have each participant listed as a row with various data in columns. I am curious if anyone has experience with creating a source sheet of contacts so that if I add/remove a participant from the source sheet, they get added…
-
IF Formula
Hello, Not sure how to create column formula for the following criteria utilizing RYGB color formatting: • 0-29% red • 30-49% yellow • 50-99% green • 100% blue
-
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",…
