-
Automate Parent Row formula and keep children formula in place
I'm trying to figure out the best way to automate a formula specific to the parent tasks and keep the existing children formula in place. The goal is for the user to be able to add more parent/child tasks without having to insert the formulas manually. The parent formula in use: =IF(COUNTIF(CHILDREN(), "Red") >= 1, "Red",…
-
How to count individuals in multi-contacts separately?
I'm working on a formula for project allocation. I'm extracting my information from two columns in another sheet, master tracker. The columns from the master tracker are Status and Project Leads. In the sheet for Project Allocation, I'm using the following: The Columns are the Status and Person 1, Person 2, Person 3, etc.…
-
Index/Collect Formula Not Working..Incorrect Argument Set Error
I have two sheets. Sheet A is a rate sheet with the columns: CEB, Client Name, Client Engagement Name, Engagement Resource, Engagement Rate. Sheet B is my weekly billable time report with the columns: Quarter, CEB, Client Name, Project Name, Date, Hours, Employee, Description, Rate, Total Cost I want to pull over…
-
Parent cell return Data based off of Children Data
Hello, I am trying to figure out how to put the following into a formula: If values in any child rows include "In Progress" then populate parent as "In Progress", otherwise... If values in child rows do not include "In Progress" or Blank then populate parent as "Not Started". otherwise... If values in ALL child rows…
-
Flagging a Date and Time Overlap Across Multiple Days
Hi all, I need some help with a formula which I believe I almost crack but I don't know what I'm still missing here, I want to flag overlapping tasks across multiple days and times, as you can see below, I have different columns: Start date, end date, start time, end time. Start and finish are just switching the time…
-
IFERROR for #DIVIDE BY ZERO
Hi! I'm calculating the averaged RYG in parent rows that's tied to our Status column. Until the status changes to something other than "Not Started", the parent row displays #DIVIDE BY ZERO. I'm trying to use IFERROR to display blank but I'm getting an #INCORRECT ARGUMENT SET. I'm probably missing a parenthesis somewhere.…
-
If/and referencing multiple sheets
Looking to express the following: If [ID#] in sheet A is equal to [ID#@Row] (in sheet B), AND [Progress] in sheet A is between 90 and 100, show "Published" in [Progress] in sheet B, If [Progress] in sheet A is between 15 and 89, show "In Progress" in [Progress] in sheet B, If [Progress] in sheet A is between 0 and 14, show…
-
If function based on multiple columns
I have done formula on column 3 and 4 if column 3 returns yes and column 4 is blank, the status will return from column 3 but if column 4 has yes in it, the status will return from column 4 ignoring what is column 3 I want to add the column 5 in the same formula with the same principle if column 5 has yes, status will be…
-
Formula to generate a weighted average score
Hello! I'm trying to create a formula that will generate a weighted sum total across a couple of criteria columns after each column has been assigned a score. Each of the columns will have a drop down where we can select a score (from 1 - 10). Each column will have a different weight: Enterprise Priority: 35%; HR Priority…
-
Why does my MID formula work when I subtract the incorrect length?
I made typo in one of my formulas but it still returned the desired result. I want to understand so I don't end up with unintended results down the line. My goal is to extract the data that follows "Set 4:". So in this case the formula should return "0101". It should always return the data that follows "Set 4:". My…
