-
Help with IF functions
I want to create a function that basically says If, the Due Date is is more than 14 days past due I want red If " " is less than 14 yellow On date green This is what I have so far =IF([Due Date]@row < Today(-14), "Red"), IF([Due Date]@row < Today(-13), "Yellow"), IF([Due Date]@row, TODAY(), "Green"))) Getting a #UNPARSED…
-
Conditional formatting - Calendar view
I would like all of the events to be a different color on the calendar I am creating however if I use "type of event" as the condition, they all end up the same color on the calendar even if I set them differently. I have tried setting AND conditions as well but they end up the same color. For example, if I set "Moda…
-
Exclude Rollup / Summary Tasks from Summary Sheet
Hi All, My Smartsheet has Rollup / Summary Tasks, Deliverables/Tasks and Milestones. On the Summary Sheet, I only want to count the actual Deliverables/Tasks i.e. not the Rollup/Summary tasks or milestones. I found how to add a column that determines whether a row is a child or a parent using - =IF(COUNT(CHILDREN([Task…
-
If cell is empty or blank, I want to automatically enter a 0.
I'm not sure how to automatically enter a value of zero if a cell is empty. Is this possible? I tried to enter the formula below (and various iterations of it) but I get errors. The error for below was "unparseable". =IF([% Complete]1:[% Complete]17, ISBLANK, ("0")) Anyone know if this is possible? Thx, Deb
-
Is autofill possible in this scenario? and how
I am working on a calendar form and I would like if I entered a specific name of an event, that the next column would autofill a number. For example, if in the "event Name" column I type "Marathon", I would like the next column to autofill with 18000 for the expected participants. Is there a way to do this? Thanks
-
How to effectively use COUNTIF with multiselect column?
I'm trying to count each unique value selected in any given row of the column, however, the selected cell is coming back as one long string. How do I count only the unique values, programmatically? I'm using the formulas shared for creating a list of distinct values given any column, if that helps (I can't remember where…
-
Alerts based on lookup
I have a column in a sheet which is a Multi Select Dropdown with a number of roles listed. I want to set up alerts to individuals, sending them the rows in which their role is mentioned. The email addresses relating to each role are held in a separate sheet. Is there any way I can use lookup to send the alert to the…
-
IFContains formula
Hi I am having an issue writing a formula. I have dropdown fields on my sheet, with either "yes" "no" or "n/a". I want a checkbox formula that ticks when any of the dropdowns contain "no". Then another that specifies the information related to the field next to it that contains the answer "no". FYI - For each question, as…
-
Index/Match based on a condition in another cell
Hi, I'm trying to get INDEX/MATCH to pull in related data (based on Asset #) from different sheets. Here is a sample sheet. When the user types in the Asset # (TYA10229) the formula pulls the description from another sheet (Roadmap Asset Catalog) based on a matching Asset #. This works great. Here's what I'd like to do -…
-
I am having issues referencing multiple columns using the IF statement.
I have 2x formulas: =IF([Delayed?]@row = "Customer Delay", "Customer Delay") =IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress"))) Both work independently from one another. I'd like for this formula to used in the 'Status column…