-
Formula is not persisting when value is removed from cell
Hello, We are having an issue with cell formulas. The cell formula is erased when we change or delete a value in a cell. Here is the formula we are using: =IF(Status4 = "Complete", "In Progress") The formula should first check the cell status in Row 4, then change the cell status on the row where the connected cell is…
-
Formula Counting Created Date as Following Day When Recorded After 5:30 PM PDT
When creating a metric sheet that evaluates entries in another sheet, I am attempting to write a COUNTIF formula that counts all entries for that day, but any entries recorded after 5:30 PM PDT are counted towards the following day. I created a helper column that converts the system column into just a date, and that fixes…
-
If Statement Returning Different value if a cell has a single digit in it versus a two digit number
See the sheet below. In the SLA Tally column I want it to look at the [Age of Defect] and if it is larger than [P SLA] return that it is "Outside SLA". If you see on Row 4, it works correctly. It is recognizing that age of the defect is 7, and it is above the SLA range of 7, and returning "Outside SLA". However, when the…
-
INDEX/Collect function with two criteria returns #invalid value sometimes...
Hi There, I have a status update sheet that has many columns. The three relevant here are: 1) Date Column = {createdDate} 2) Checkbox = {PSR} 3) free text column = {Overall Commentary} ----- In another sheet I am trying to collect the row with both the latest date and has the checkbox ticked. Here is my formula:…
-
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…