-
Date Formula for the Monday following a Date
I'm looking for a formula or automation that calculates the date of the Monday following a specific date. The initial date may be a Tuesday, Wednesday, or Thursday, but the date I need to "fill in" will always be Monday, as it's a due date. In the example below, the Schedules Due Date column needs to be the Monday after…
-
Nesting DISTINCT into an AVERAGEIF formula
Is it possible to use the DISTINCT function within the AVERAGEIF function? I need to average the number of days a PO has been on order if a specific purchase group number was used to create the order. The problem is that our data is pulled from SAP, which displays the POs line by line, meaning that there are multiple rows…
-
How would I write a formula to locate duplicates in 1 column and the difference in another column.
I have 1 sheet that has duplicated Classroom IDs but different scores on each line. I need to flag any instances during the same term of a difference over 1.00 higher or lower with the same Classroom ID. I need to find the repeat instances within column "Classroom ID" that occur during the same Column "Term Name" and then…
-
How to use COUNTIF on a Smartsheet Report
Hello - I have a report that manages all past due, in progress, and upcoming tasks sorted by team member. I am trying to use the COUNTIF formula on my Metric Sheet to count the number of past due tasks by team member in the report. My goal is to use my Metric Sheet to gather the number of past due tasks by team member and…
-
moving Just a Column to a new sheet with Automation
is there a Way that i can Move just a Column in a Sheet to another Sheet that will just Grab the Column. i would like it to work like the Automation that it Grabs the Row any time something is Triggered. but i would like it to just Move the 1 Cell from a Column not the Row as i don't need anything else just what got…
-
Using OR for different ranges
Hello, I have a formula I'm trying to improve: =COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {End Date}, >=DATE(2025, 8, 15)))) + COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1,…
-
Cross Reference Sheet Formula Help
I am needing help on a formula. I am trying to pull a clinical placement status from one sheet over to another but only if the hospital name and unit department matches. I also need to match the date to the cross reference sheet over 16+ columns that could have that date match. I am currently using =IF(HAS({Day of week…
-
IF(MATCH with Multiple Criteria Across Sheets
I am attempting to create a formula that will search another sheet for a product code and as long as the product code isn't listed as "Canceled" on the other sheet, return "Yes". I started with =IF(CONTAINS([Product Code]@row, {Upcoming Products}), "YES", "NO"). When I tried to add the condition, I got a little lost on how…
-
How to create either a conditional formatting and/or use IF(AND) Formula for Attendance Tracker?
So I'm trying to create a formula or create conditional formatting for attendance. Every 1 Unexcused Absence within 45 days is 1 Occurrence that must be documented with the employee. Every 2 late arrivals/ tardies within 30 days count as 1 Occurrence that must be documented with the employee. So if my employee was…
-
Counting occurrences of a string in a row
I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to exclude. Is it possible to get a count of the "N/A"s in the sheet with multiple ranges to exclude some rows? =COUNTIF([Column…