-
Potential IF/AND with multiple criteria (each listed multiple times)
Okay, let's start out by saying I am so stuck I have no clue on how to create this formula! LOL Okay the formulas will be added to the PA - CAPA List sheet. The Training Report is the data source. This is just a small sample of my data (there are about 25K rows). In the sample on the Training Report you can see the names…
-
Formula to Calculate assets from Week Commencing Date from previous week.
I’m trying to work out a formula to calculate how many assets were created in the previous week from another sheet. I’m using a Week Commencing date which is the first Monday of each week and entries are being dividing into w/c based on when they were submitted to Smartsheet. How can I return the number of assets from the…
-
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,…
