-
Auto update target sheet status from source sheet
I have an Issue Log sheet (source) that if a row status is equal to either "Assigned" or "In Progress" then I want the Testing sheet (target) to update it's matching (match is by ScenarioStepID) row status to "Failed-Issue Reported." I also need the same basic updating, but if the source row is "Retest" then the target…
-
IFERROR + IF/ISBLANK
This formula is almost perfectly pulling downtime by examiner and production date: =IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) There are a few records that are not in error, but the result is a blank, however, I need those…
-
INDEX COLLECT formula error with criteria that starts with zero
I have a bill of materials sheet that will pull in the cost of an item based on the model number, using an INDEX COLLECT formula referencing a separate pricing catalog sheet. All works well, except for when a model number is an integer that starts with a zero, and Smartsheet automatically adds a "hidden" apostrophe to the…
-
How do I use COUNTIF to calculate Date Added?
Hi everyone, I am attempting to add a sheet summary that totals my 'date added' column broken down by year. Here's what I've got: =COUNTIFS([DATE ADDED]:[DATE ADDED], >=DATE(2022, 1, 1), [DATE ADDED]1:[DATE ADDED]18, <=DATE(2022, 12, 31)) This keeps spitting back an 'incorrect argument set'. Any ideas on what I'm doing…
-
IF(Contains)
I am using the IF(Contains formula to look at values in the Numbers to Include column, which is a multi-select drop down column, and it if finds the value to add a number to the Office Phone column. =IF(CONTAINS("Main Office: 303-932-0757", [Numbers to Include]@row, "303-932-0757", "")) This formula is in the Office Phone…
-
Where do I put the IFERROR function to correct for year?
I have a column of dates. New rows are being added over time. I count how many occur in each month. In a separate metric sheet, have 12 formulas, 1 for each month, that look like this. =COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Date Column}, MONTH(@cell) = 1) for January =COUNTIFS({Date Column},…
-
AVG(COLLECT) Formula with Multiple Criteria, Including Date Range
I'm trying to write an AVG(COLLECT) formula on a separate metrics sheet to calculate the average number of days in a month for all rows with: Priority = High Polling Question = Yes Start Date >=1/1/2024 Closed Date <=1/31/2024 I've tried numerous formulas, the latest of which is…
-
Consolidate Multiple Column Cells Into 1 Based on Cell Value in Row
I am working on a telecom cleanup project and have an export of all the telephone numbers at each site. I'm wanting to take the telephone numbers in each cell and combine them into 1 based on the branch number. The closest thing I could figure out was to run a collect function based on the branch number but am getting an…
-
Help with IF(AND) function
I have been trying to figure out where the issue(s) with my formula is awhile. I have tried to break the formula down in sections too, that didn't help. Is anyone able to help with my formula? What trying to do use RYG symbols: Refer to the Stage of Project Column, IF = Kick-off AND Wait List & PM Assignment = "" (blank),…
-
Sum if status is approved and end date is in the future
I am trying to get the sum of approved projected expenses only. I can't figure it out I have been trying SUMIFS and SUM(AND formulas but can never get it to return a result. I can get one or the other to work, but it's important that only approved future expenses are summed. Below is a screenshot of the columns I'm working…