-
Formula 30-days in future if priority = "high"
Hi. I am absolutely no good at formulas! I'm hoping to have a due date 30 days in the future from when the row is added to the sheet, only if the project priority is "high". This will trigger a notification to the assigned PM. All other priority ratings should have a blank in this due date column (or an error, so long as…
-
Return value of another column if checkbox is checked
Referencing the below example, I'm looking for a formula that can return values in other columns when the checkbox is marked in the first column. I have a sheet with many tasks pertaining to a project but only certain tasks are tracked by our executive team. In the example below they only want to see Date 1, Date 2 and…
-
Need help with IF formula
Hello! I want to use an RGB Symbol formula that first looks at a 'Completed' cell and if "checked" returns green, but if not checked it moves on to look at a "Date" column to return RED, YELLOW, BLUE or Upcoming. Here is my current formula: =IF(OR(COMPLETED@row = 1, "Green")), IF(DATE@row < TODAY(), "Red",…
-
AVERAGEIF value between a date range
Hello, I am creating a metrics sheet that cross references an archive sheet and am looking to pull statistics within monthly ranges. I want to return a monthly average of the Total ADL Score within a monthly range which is determined by the ARD (Due) Date column. This data is returned to my metrics sheet (Sheet name: Avon…
-
Return Date (in text column) from a range based on 2 criteria (is not blank, and match by task name)
Hello, I need assistance with following formula. I have a sheet that tracks tasks by type of job. Each type of job is the parent, with children tasks underneath. Each type of job has a few standard tasks that are the same across all job types. What I am looking to achieve is a formula that will return the Due (or Complete,…
-
COUNTIFs formula with Date Range
Hello, I am trying to create a monthly trend report. The report function is not specific enough for what I need, so in order to do this, I have created a metrics sheet that references 21 different originating sheets. To fill my metrics sheet, I need to use a COUNTIFS formula to reference a date range. I've tried it a few…
-
Fiscal Year Calculation
Hello, I am trying to have a column that calculates a fiscal year automatically based on whether the parent Completed Date falls before or after July 1. Something that follows this logic: If the parent "Completed Date" month is less than 7 (July) display "FY 'Parent Year'", otherwise display "FY 'Parent Year + 1'", if…
-
Function to check box if lookup is true
Hi Is it a possibility where a column contains checkboxes, for a function to sit behind that that if a lookup to another sheet returns a true value, the checkbox is ticked? Specifically for the lookup to be: On Sheet1: If the value in col ID and the date in col Next Inspection Date appear in sheet2 (below) in the cols…
-
Reports Not Updating Live
I'm trying to shift our company towards using reports more, instead of multiple people interacting with sheets directly (and thus making version control difficult when minor items are changed, particularly on Control Center provisions) -- To this end, I've been given feedback about reports that indicate that they do not…
-
RAG Conversion formula
hi All, Hoping someone can help me out... -Column A is a Forecast RAG Traffic Light column -Column B is a formula that converts these RAG to a numeric value eg (R = 0 , A = 0.5 , G = 1) -Column C is a $Day Rate -Column D is a $Total Rate My issue is when I create a formula in Column D of =ColumnB*ColumnC I am presented…