-
Index and Match Gives Random Results
I am building an audit sheet to show where end dates and status do not correlate correctly. I've built and index and match for the End Date column that functions correctly, but cannot get the Status to Index and Match consistently. Source sheet sample below. The Index and Match seems to work sporadically for the Status…
-
Remove "In Progress" from formula, but comes back unparseable
Good morning! I would like to change the behaviour of the below formula that defines rag status... =IF(Status@row = "Complete", "Gray", IF(AND(Status@row = "In Progress", [Due Date]@row > TODAY()), "Yellow", IF(Start@row > TODAY(7), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow")))) I would like to remove the…
-
Formula to Calculate % Complete
Hi there, I have created a task sheet and want to calculate the % of completed tasks. Below is a picture of part of my sheet (there are a lot more tasks below). When an item is either ticked Yes or No, the Date field is auto populated. Once the Date field is populated the Completed cell turns from Red to Green. I would…
-
My column formula =COUNT(DESCENDANTS()) returns #BLOCKED in one cell. Is there a limit to the count?
All other column formulas work fine. The only reason I can think of is there may be too many descendants? The value returned should be 1540. Is there a limit to the COUNT function?
-
Cross-Sheet SUM formula roadblock -- any formula wizards who can help?
Hello! I'm currently using a cross-sheet formula to pull in multiple cells of numeric information in a column on 1 sheet and return as 1 cell in another. I'm doing this using a JOIN(COLLECT formula. =JOIN(COLLECT({Dev Spend Auspice Tracker - Development Fees}, {Dev Spend Auspice Tracker - ID}, ID@row), SUBSTITUTE(HELPER#,…
-
Moving children rows under a different parent row
Is there a way to set up a formula that will move project status "completed" from under one parent row to another parent row named "Completed project" once the project status has been selected as completed?
-
Possible to return original entered value in formulas?
Hi there! I'd like to use a simple IF expression to test a condition in another column. If that statement returns FALSE, I'd like to KEEP the originally entered data in the cell. Is something like this possible? The Unfilled column is a check box to indicate that a staffing shift is currently unfilled. If the box is NOT…
-
Calculating Difference Between Multiple Dates
Hello, Curious if there's a single formula to calculate difference between Planned and Actual for both start date and end date? Trying to get the Total Time +/- value. Thanks.
-
Countif function
Ok so...trying to get this data from the source file below which feeds into the computation sheet below it to represent 3 graphs in my dashboard (Click total, Unique click, Opened) for each previous month....you can see my countif in the computation sheet but it errors out. Basically each leader i want a chart that shows…
-
Pull a value from sheet with either multiple IFs or IF combined with V look up
Hi - based on 2 different criteria I am looking to pull a date from a separate sheet. I have these attempts but had no luck - Any help suggestions much appreciated. Ryan