-
How do I create an IF functions that will change my status color if information is in the cell
I am trying to have my event status which is (symbols) update to gray, red, yellow, or green depending on if 2 cells contain information (Green), or one cell or no cells have information and is more than two weeks from the date (yellow), one or no cells have information and is less than two weeks from the date or event is…
-
Cross-sheet Formula Limitations and Possible Work Arounds
First, what does the term "distinct cross-sheet reference" mean? I've just run into a message that I've exceeded the 100 distinct cross-sheet references and am looking for a possible workaround. Background: We have three sheets with various information about our employees. Basically, one sheet is the Employee Master List.…
-
Totaling # the of projects that are older than 30 days, 365 days etc
Goal: how many open requests are older than the specified # of days old, this formula returns a result, just don't think it is the correct one as when I try to duplicate it and ask for requests more than a year old, the number encompasses ALL open projects in the last 365 days not the ones specifically older than a year:…
-
How do you calculate the average number of days between a submitted date and a start date?
Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a date range of when they need their request completed by. I would like to calculate the average amount of days people provide from the day they…
-
Check a box if 3 criteria (checkbox columns) are met
I'm trying to identify registrants for a training who no showed using a checkbox column. I have checkbox columns for the following critieria: Invited Attended Declined I'd like a formula for my No Show column to return a checked box for anyone who: Was invited (box checked) Did not attend (unchecked box) Did not decline…
-
Sum of children while keeping column formula
In my sheet I have columns with formulas that I would like to keep as applied column formulas. I also categorize my row's by creating children under a labeled row. Within the labeled row I would like to Sum up the figures contained within the columns that have column formulas however when I try to do that, the system wants…
-
Function like UNIQUE in Excel?
Does Smartsheet have a function similar to the UNIQUE function available in Excel? The function allows you to select a range, and then provides a unique list from that list. In Excel Combining: SORT, UNIQUE & FILTER will provide a unique list excluding blanks that is alphabetized. I am constantly using that function within…
-
I would like to count the total values from a multi select column, based on another columns criteria
I apologize but I cannot seem to wrap my head around this. My goal is to count the total values in a multi select column based on another columns criteria. I figured I could combine the use of CountM and CountIFS but I have not been successful. Here is an example. The goal is for me to determine the total # of Reps Per…
-
"Review status" based on multiple checkboxes
Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…
-
Parent Status based on Children Statuses - Need help refining this formula
Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…
