-
Average between time frames. Formula error using AVG(Collect)
Hi again everyone! I am hoping someone can assist me. I tried a formula @leonastomenio sent me awhile back, which worked perfectly, but now I have another situation that this formula does not work. I am trying to average the Days it took to complete an action if the cells are not blank, and if the Date Last Action Taken is…
-
How to ignore blank cells in health formula
I have a working formula for task health based on % Complete and Target Completion Date (see below). I'd like this formula to ignore any rows with blank Target Completion Date and display nothing in the Health column if there is no Target Completion Date. Right now it's defaulting to Red. I know I should add ISBLANK, but I…
-
Calculating Total Booked Margin with Multiple Percentages
I'm trying to calculate the total margin with an original booked margin on a project with a change order booked at a different booked margin. Project Steve booked at 50% margin, CO1 booked at 25%. Combined, what would the Current Booked Margin be? How would I write the formula for this?
-
Can a Sheet Summary field be included in a workflow automation?
In the SUBJECT LINE of a workflow automation UPDATE REQUEST email, I'd like to include a sheet summary field by using {{sheet summary field}}. Is this possible?
-
Why is my dashboard link sending me to a sheet instead of the dashboard?
We have a startup intake sheet in Smartsheet connected to Salesforce to process all of our new business wins. Once a win comes in and I approve for a project plan in control center , a site plan is created and a dashboard link appears on the intake sheet. Up until the other day the dashboard link always took you to the…
-
Calculate start date based off of end date
Hi All, Can someone help me with a formula? I've searched the community and haven't been able to find an answer. I already have a formula in place to calculate the end date based off of the start date and duration of project. However, if I have an already defined end date I'd like to calculate the start date based off of…
-
Checkbox with IF AND formula- satisfy two criteria simultaneously
Hi, I want to add a formula to a checkbox column, the purpose is to automatically check the box once the cell satisfies two criteria simultaneously. Criteria 1: Phase column=Milestone Criteria 2: Finish Date column<= 03/11/2024 I have tried a few IF AND formulas but still can't get the result desired, loosing temper now :(…
-
Formula to identify the first duplicate record
Formula to look at "Client Name" and if duplicate only return Yes for the first one, if not duplicate then just return Yes but I'm getting an error. Help? =IF(COUNTIF([Client Name]$1:[Client Name]@row, [Client Name]@row) = 1, "Yes", IF(MIN(COLLECT(ROW([Client Name]$1:[Client Name]@row), [Client Name]$1:[Client Name]@row,…
-
Identify Duplicates then Add a Character (A, B, C, etc.) as a Suffix
Hello Smartsheet Community! I have searched through here a bunch but I can't quite find what I'm looking for. I have a sheet with order #'s (Order #) and if there are multiple services to schedule, they each need a row. So my order # is duplicated. I have a column that counts if the order # is duplicated (Order # Count). I…
-
Formula referencing multiple criteria
Hi, I am a little stumped on where to start with this one. I have where I want this sheet to count each of the columns of another sheet if certain criteria are met. For instance, I want to know the sum of Beginning Case Load - Active Status if the role is nursing and the date helper column is 2/2/24 (the return response…