-
How to use Vlookup or Index/Match to automatically mark a task complete in destination row
Hi! I have a task tracker in which things are marked "Complete" from a dropdown and then checked off. The checkbox has conditional formatting that puts a slash through the entire row. Some of these tasks are delineated as "projects", but I've created an automation to copy a row if it is labeled a project, so that anything…
-
Anyone know why this IFERROR formula isn't working?
As you can see I want the formula to return a blank if there is an error but it's still returning "#BLOCKED"
-
Update formula so it ignores empty rows
Hello. I am using the below formula to mark whether a task is overdue or not, and then highlight using conditional formatting those overdue tasks in red: =IF(AND(Finish@row < TODAY(), Status@row <> "Complete"), "true", "false") I would like to update the formula to ignore blank rows. How may I accomplish this? Also, open…
-
Formula for quarter based on finish date
I want to create a formula that will auto-populate the Quarter column with Q1, Q2, Q3, or Q4 based on the date entered in the "Finish Date" column. For instance, if the "Finish Date" is 4/15/2021, I want the Quarter to be "Q2".
-
Within X amount of time from Today
Hello, I am trying to find a formula that will tell me if a date is within 4 Years of today's date and populate "YES" or "NO" in a separate column. (Example: To help specify if a product is in warranty that lasts 4 years, and populate "Yes" in another column if the date entered is within 4 years)
-
Count IFS and Count IF
I have a formula that is counting, for a specific criteria (see below), in a specific column; however, now I need it to look in a 2nd column and also include it in the COUNT; however, I do not want it to be "double-COUNTED". For example, if the amount will always appear in Column 1 and then eventually appears in Column 2…
-
How to create template dashboard and summary grids with formulas?/ Is it possible?
I need to create a set of templates that are personalized to our needs. The needs are the following: - We have a template that is pre-set with column names that is populated by us. 1. From this template after it is populated I need to create a summary template in a grid containing formulas. 2. From this Summary template I…
-
AVG Collect adding Month and Year
Hello All, I am looking to average a Likert scale question of 2 hospitals together, from my list of 9 hospitals. How do I add the month and year so I only average the score for January 2024 =AVG(COLLECT({Question 1}, {Hospital 2}, "Hospital A", {Hospital 2}, "Hospital B")) I have done this in the past,…
-
Average collect w/ 3 criteria
I'm attempting to average the %age passed for a specific month and year for a specific team. I successfully used the below to average the total by month and year, but can't figure out how to add the team. =AVG(COLLECT([%Passed]:[%Passed], [Month 2]:[Month 2], =1, Year:Year, =2024) I tried the below, but am getting a…
-
Logging time on hold
Hi, I am looking for a way to log the time a row's project status column is changed to on hold and then stop counting when the status is changed. It would be possible for the project to go on hold multiple times. Any help would be much appreciated. Thanks!