-
Referencing date and time from another sheet
I am trying to reference an automated creation column from sheet A to populate date AND time in a column in sheet B. I have been successful in the reference, but then have to format the receiving column to date format due to invalid data type. Problem is I need the time to populate as well. Also, when writing this formula…
-
RYG Symbols in Child rows to update parent row
Hi, I am looking to populate the status of parent row to symbol: Red, Green, Yellow Parent Row: Red Symbol = If ALL child rows are red symbols Yellow Symbol = If ONE of child row is yellow or green or all child rows yellow (child row may contain red) Green Symbol - If ALL child rows are green I created this formula but…
-
Using COUNTIFS
Hi, I am using the following formula to count those occasions when CSOD (unit) is impacted by CFCD (unit) and the status of the impacts is either 'conceptual' or 'complete' and it does not work. If I limit it to only counting 'conceptual' it works. What I am missing to count more than one status?…
-
Nested IF function using negative percent values & RYG balls
I'm using a nested IF statement to determine the colour of RYG balls in Column 'YTD Health', using data from an adjacent column of percentages. The percentages listed are a combination of negative and positive values. What I want to happen is this: Values 0 to -40% = Yellow Values less than -41% = Red Values 0 and greater…
-
Counting only if conditions are fullfilled
Hi everyone, I was wondering if someone can help me with a formula for counting stuff? I've got let's say 4 stages of study progress - start-up, active, LTFU and completed. Now I've got a helper column to help me identify if the project code is unique as multiple resources may work on the same project. If it says "1" the…
-
COUNTIFS one OR another criteria
Hello, I am trying to find the number of cells in a column that are blank OR do not contain a specific word. I believe the way that I have it right now would be AND rather than OR. Any suggestions? Need: If Accounting column is checked and Bio Status is blank OR does not contain "Posted" I'm currently getting 1 as the…
-
SUMIFS with Multi-Select Criteria
Trying to correct my SUMIFS formula include two criteria from a multi-select column. I'm currently getting UNPARSABLE every time try OR, AND, or HAS. Here's what I have so far with my current criteria, I just need to add an additional option for "CXL - COVID19" in addition to "Approved": =SUMIFS({FY Amount}, {FY}, FY 20 ,…
-
Cell linking and updating a column
I have a master sheet that contains a column called "Product Family." This master sheet is updated weekly, and some of the Product Family rows change - added or deleted. The cells in the Product Family column on the master sheet are linked to another worksheet (called "Curation Audit") - also in a "Product Family" column.…
-
Calculating costs for a task over more than one month
I'm trying to come up with a formula to calculate the labor costs per month in one sheet ( 12 columns, one for each month), by referencing another sheet, and referring to the start/finish columns of said task. E.g. Task 1: Start = Aug 27, Finish = Sept 5. So in my cost sheet, the formula needs to refer to the start date…
-
Using IFS statement with date and check box
The simple statement is I want to have a sum of work hours in a department, that is still incomplete, during a calendar period. Currently the formula is: =SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, >=DATE(2020, 9, 1), Finish:Finish, <=DATE(2020, 9, 30)) * 8 A suggested version has been…