-
Trouble referencing values in a "CONCATENATE" cell
Hello, I am having issues trying to reference the values in a CONCATENATE formula. I am trying to pull a "Yes" or "No" value based on the product numbers provided in the CONCATENATE cell. The CONCATENATE cell is the combination of product numbers added just for TODAY only (old lines do not show a value unless it is today).…
-
Run formula on creation only
Hello, I am trying to have a formula in my primary column to have a date-number of tickets created on this day. so I would have 11/02/21-1 in row 1, 11/02/21-2 in row 2 and so on. However, when A cell is added above it it the formula updates so 11/02/21-1 becomes 11/02/21-2. IS there a way to lock the formula to only run…
-
RGY Health Formula for Status and Due Date
I'm struggling to complete a formula to do the following: If Status is Complete, mark the health green. If Status is Not Started or In Progress and End Date is 10 days or greater, mark health green If Status is In Progress OR Not Started and End Date is within 5 days, mark the health yellow If Status is Not Started OR In…
-
Help to select a value based on row number
Hi all - I am trying to determine if a task is ready to start based on all of the predecessors being 100% complete. I have numerous tasks with more than 1 predecessor and I've parsed out all the predecessors into individual columns (pred_1, pred_2, etc) like this I'd like for the ready to start column to indicate yes, if…
-
Formula to change symbols
Can someone please help me with a formula? I want to automate the yellow ball two days before the due date and the red ball the day after the due date when the "Done" column is not checked. Thank you for your help!
-
Health Formula
This is my current formula. I have added a drop down option in my status column "NA". I have also changed my health column to include a gray light option. Can someone help me add this to my current formula. IF (Status@row = "NA", "Gray". =IFERROR(IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", [Task…
-
Need help with COUNTIFS and Count(Collect) formulas
Hi Formula Gurus, This should be straight forward but I keep getting 0 or 1. I'm looking for a count of projects do not have a Project Category of "test" and they have Year Started is 2023 (or yr 2024 in example 2) and Quarter Started is Q3 (or Q2 in example 2). The first formula below results in 0 (zero). This sheet is…
-
INDEX Collect to reference another sheet
I want to use index collect to get who worked on a job from a different sheet =INDEX(COLLECT([ASSIGNED ENGINEER]@row, [JOB NAME]@row, <>"", [JOB NAME]@row, <>""), 1) This works on the same sheet but when I try to apply it to another sheet =INDEX(COLLECT({APN/DFA COMPLETED Range 3}, [JOB NAME]@row, <>"", {APN/DFA COMPLETED…
-
Can Anyone Help on COUNTIFS Formula with Dates?
I have a calculation sheet with the formula =COUNTIFS({Sent to AIR-360}, >[FY Start Date]@row, {SR Type}, ="NTSB", {Sent to AIR-360}, >{AIR-360 Due Date}). The formula is to count dates greater than FY START DATE (Date Column), that equal NTSB (Dropdown text column) and Sent to AIR-360 dates that are greater than the…
-
Turn Date into numeric value
I would like to follow a task and see the difference between when it was planned and actually done. To be more precise. Column "Agreed Date" is used on what date the two parties agreed to finish the task Colum "Final Date" should show what day the task was really done. This should not change based on date, but should be a…