-
Copy To Another Sheet
Please help! I was curious if there is a formula that would copy a row to another sheet, only if a certain column meets the criteria. I created two sheets that are exactly the same, but one is named Overtime and the other is Branch Visit. If the Branch Visit column states 'yes' on the Overtime sheet, can that entire row…
-
Formula to check Child Row checkbox if Parent row contains "ABC Dept" (filtering by Department)
I have a "Master" project resource sheet with great grandparent rows for "Current Projects" and "Future Projects", Then grandparent rows for each "Project", parent rows for department and child rows for the individual resources per department. I'm trying to create a filter to filter resources by department across…
-
Finding date in a child when other children are not always dates
I have a parent row that I am trying to use to summarise the Children rows. One column is a date called CloseDate. Rows are added every week, that create in effect a weekly record and the CloseDate is set to the word Active until the entry the week that it is closed and then a date is added. The row is not always at the…
-
Average formula with dates
Hi all, In our sheets we track the time we spend on a specific task on a certain day. So in our sheets we have for example: Due Date: 1-07-2019; Duration: 10m We can have multiple tasks for the same day. I am trying to come up with a formula in which I can calculate the average spend over the past 60 days on a client.…
-
How to use end date and percentage complete to automate RYG status
Hi everyone, Maybe my brain is struggling with IF and AND statements, but this is defeating me. I am trying to use the end date of a task compared to today's date and the percentage complete (great than or equal to 75%) to drive my status column of red, yellow, green balls. I think I need to find a way to combine the…
-
Calculating an Average based upon a value in other column
I have a calculation to calculate the number of days between two columns. =IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), " ") It works great to return a blank if the customer has not gone live. Then I have formula to calculate the average of the cells. =AVG([Number of Days to Implement]12:[Number of Days to…
-
Average % Complete If another Column is Checked
I am trying to get the % Complete for a project by averaging the Project Phases % Complete(which are parents to tasks). * =AVG([% Complete]:[Project Phase], 1) I used this formula to only calculate the values in the row that has been identified as a Project Phase with a check.* Answer is 8.39 * =AVG(CHILDREN()) * 100 -…
-
Next deliverable date based on TODAY
Hi Many thanks in advance to start! For each row i have a listed task with a column called Period 1 with a date and a checkbox next to it than when checked marks the date green. Each date represents when that task is next due. Extrapolate that out across the row and i have many dates for every interval the day is due, ie…
-
Formula for taking an average of duplicate rows
I'm attempting to construct a formula for taking averages of multiple ratings from several raters across three vendors. Sheet 1 looks like: Rater - Category - Vendor A Rating - Vendor B Rating - Vendor C Rating Sheet 2 looks like: Category - Vendor A Rating Avg - Vendor B Rating Avg - Vendor C Rating Avg On sheet two, I…
-
SUM and IF In the same Cell
Is it possible to have a cell include both a SUM and IF statement to do the following. (I do not want use a separate status column.) I want to be able to add up several columns (Actual Admin Labour + Actual Engineering Labour + Actual Mechanical Labour) into a Total Actual Labour column and then add an IF statement that…