-
Formula Needed for Parent to Inherit Child Value
I don't think this is possible, but I need a formula where a parent row Status value reads "No Active Projects" until it has a child row below it with an "In Progress" status at which point the cell should just be blank. See image below. I use the "No Active Projects" marker to determine whether or not that department…
-
RAG Status - Formula
Hi, I have currently formulated the following formula, =IF([% Committed to Date]1 - [Project Status %]1 < 0.25, "Green", IF(AND([% Committed to Date]1 - [Project Status %]1 >= 0.25, [% Committed to Date]1 - [Project Status %]1 <= 0.75), "Amber", "Red")) which is working efficiently, but problem occurs it shows me the…
-
If cell is empty or blank,
Hi, I have currently formulated the following formula, =IF([Days (Difference Forecast - Baseline)]1 <= 5, "Green", IF(AND([Days (Difference Forecast - Baseline)]1 > 5, [Days (Difference Forecast - Baseline)]1 <= 10), "Amber", "Red")) which is working efficiently, but problem occurs when the cell is empty, and it show me…
-
Counting Quantities of Numbers
I am trying to build a column to count the # of times a predecessor appears in the predecessor column. The below formula has almost gotten me there, but it is also counting predecessor cells that include 1, but not exactly 1 (like 12, 21, 100, etc...) =COUNTIF(Predecessors:Predecessors, FIND(1, @cell) > 0) Is there any way…
-
Date Calculation Question
Can anyone share with me on how I can calculate what I call the 'Next Quarter Projects'? For example: I have a start date and end date columns for each project, and I want to create a Filter/View that shows me the projects that are due in between a date range(ie Q2, Q3, Q4, etc) Can anyone help? Thanks!
-
Flag if Multiple Dates in Same Column
I'm attempting to set up conditional formatting to highlight cells if there are duplicate dates in the same column. (For a spreadsheet managing event bookings, want to highlight if multiple requests for same day) I tried an IF/COUNTIF function in a new checkbox column but I keep getting #UNPARSEABLE. - Column name is Mtg…
-
Using MAX Formula- How to formulate it to capture infinite rows
Hey Guys! I have a Smartsheet that is connect to Jira. I have added two columns; Created On (with creation date column) and Last Create Date (date column). I am trying to populate a formula that pulls one text box of when the sheet was last updated. When I attempt to enter =MAX([Created On]2:[Created On]???) it wont work.…
-
Formula Unparsable
Hi there, Trying to return the amount of workdays between two dates if the today-date is during that same year, else return a "standard value" but im getting an error, anyone with better formula skills who could help me fix this? =IF(AND(Today2 >[Start 2020]2; Today2 < [End2020]2); NETWORKDAYS([Start 2020]2; [End 2020]2) -…
-
COUNTIFS a range with multiple assigned groups
Hello all, I am trying to COUNTIF a range that includes multiple assigned groups in some cells. I would like to count rows that include "Dept 1". There is one row in particular that contains two assigned groups - "Dept 1" and "Dept 2" - that is not being counted This is my formula: =COUNTIF({Sheet Range 4 - Department},…
-
Get all Parents
I tried to count all parents inside a sheet but since now it was only possible with an additional column. The formula I used to check if it was a parent is this one: =IF(COUNT(PARENT([Task Name]2)) > 0, "false", "true") And to calculate how many parents are existing, I just counted the cells, where the value is "true" My…