-
Date Range in formula unparseable
Hi I would be very appreciative if someone could help me solve this date range formula. =COUNTIFS(Status:Status, "Complete", [IS&T Services]:[IS&T Services], "<>Public Safety", [IS&T Services]:[IS&T Services], "<>Cross Functional", [IS&T Services]:[IS&T Services], "<>SubProjects and Tasks", [Actual Finish]:[Actual Finish],…
-
Formula to color RYG balls based on date/deadline
Hello, I'm trying to alter the color of our RYG status balls based on the proximity of the date. For example: Yellow - if today's date is within 5 days prior and 5 days after the deadline date noted Red - if today's date is 5 days + after the deadline date Green - if today's date is before the 6 days + before the deadline…
-
Name to Email with exception
Hello, I have a function that turns a cell with a name into an email address for supervisors. However, some supervisors are in training and have an abbreviation at the end of their name, is there a way to come to the same email result? The cell text would be First Last (T1) (number after T is different for each person)…
-
Formulas in Contacts Columns
I have researched this issue through multiple earlier discussions, but I can't find a permanent or reliable solution. I am trying to use formulas to populate contacts through multiple stages of a workflow- currently 2 sheets- and cannot replicate the successful behavior documented in many other threads. Sheet 1: Job…
-
Subtraction across two sheets if row and column match
Hi community, I have separate sheets for tracking monthly budget and actual expenditures. They have identical layouts (see below). Problem is, the budget goes to 2024, so I really need a formula that can calculate the variance between budgeted and actual. The logic would be something like: =budget(where row = "Feas-591:…
-
Triggering the Flag (symbol) based on 2 Conditions
I want to trigger the Flag symbol based on the conditions of 2 other cells: Completed COUNT Days If the COUNT Days are < 1 AND the COMPLETED is not CHECKED then FLAG = 1 (Red) (ie. still an active action) If the COUNT Days are < 1 AND the Completed is CHECKED - FLAG = 0 (White) (ie. Action is Complete no need to issue a…
-
It's Only Blocked Until I Kick It
I have a formula that leverages some sheet summary formulas and returns a true/false to support a report filter. When I change data in the sheet the sheet summary formulas sometimes return a #BLOCKED, but it is not consistent. When it does, if I go into the formula and edit it (without actually changing anything), then the…
-
How to exclude specific statuses when automating RYG balls?
Hello, I am trying to automate row-level RYG balls based on date, percent complete, and excluding specific statuses from the automation. I am able to automate the RYG balls based on date and percent complete using the following formula: =IF([Start Date]@row > TODAY(), "", IF([% Complete]@row < 0.5, "Red", IF([%…
-
Highlighting duplicate data based on two conditions
Hi, I want to make a formula to highlight whenever to cells contain the same data on the same date. I'm working with a marketing calendar and want to find a way to highlight whenever two campaigns are planned to go out to the same country on the same date. So, if two (or more) cells contain the same date as well as the…
-
Multi Drop Down Formula
Hi everyone, I am trying to put together a formula for an ethics check process. The user needs to select all of the items in the drop down list in order for the client to pass the ethics check. I would like to set a formula that automates Red, Amber, Green. I would like the following; Green if ALL of the options are…
