-
I'd like to create a column that generates a number of working days between the finish date in a row
I'd like to create a column that generates a number of working days between the finish date in a row and today. So, if today is Sept 15, and a task is supposed to finish on September 18, the cell in the column would display "3". What formula should I use? Similarly, is there a variable in SmartSheet formulas to represent…
-
Sheet Summary won't count only rows with no ancestors
Can't seem to figure this one out. I would like to count rows that have a Status of 'Complete', and are not a child of another row. My formula is this: =COUNTIFS(Status:Status, "Completed", Status:Status, ANCESTORS() = 0) What am I missing? It keeps returning just zero. I've tried pointing the second range at the 'Task'…
-
Fiscal Week Formula
Hello, I am looking for a formula to calculate the Fiscal Week duration of a project. The formula should be in the highlighted cells and should return the Fiscal weeks that have a value in the FW column. I am using this formula, but it returns Invalid Data Type =WEEKNUMBER([FW 37]1:[FW 52]1, 1, 0)
-
Convert Last, First Name to First Last Name
I have a contact list of Last Name, First Name; i.e. Doe, John. I would like to create a helper column with First Name Last Name; i.e. John Doe. Is there a formula to do that? Thanks in advance!
-
Looking for suggestions - master formula errors report
Hi all, looking for suggestions based on others' experiences. We have a series of dashboards and their respective back end sheets that occasionally throw formula errors for various reasons. I'm hoping I can leach some ideas from the Community on creating a master report/notification that informs us when any error occurs.…
-
Formula not returning correct data
Hello, I'm trying to count the number of projects by project owner and when it was completed. Below is my formula. It is returning data, but not the correct data. I'm sourcing from another Smartsheet. How can this be adjusted to work properly? =COUNTIFS({Completed Project Owner}, [Project Owner 2]1, {Completed Projects…
-
Index and Match
I am trying to use Index and Match in place of Vlookup and I find that when I am trying to update the reference all index and match formula's get updated to the most updated reference. Example: I am trying to pick up start and end date for one record. When I index and match reference to start, the end date also becomes the…
-
Manual Pivot of 400 unique items
I have a set raw set of data that is divided into 4 categories (i'll call it level a), and further divided into 400 (level b) more categories, each of which has a cost. Each item can repeat itself over an over, and the same item can belong to multiple categories of level a (but not level b) example set of data A B Cost 1 x…
-
Index/Match - Return the lowest row for multiple matches
I have an automation created that copies the row from the "Wetcast" sheet to a "Discard" sheet. From the Discard sheet, I want to return the "Ready to Ship Date" at the bottom most row into the "Previous Ready to Ship Date" cell in the Wetcast Sheet. The identifier/match value is the "ID" (GWC####). This is the formula…
-
Formula help - how to leave blank when Actual Finish is empty
Can someone help complete this formula? - looking for the 'Task Health' column to be empty when Actual Finish is blank, meaning task is not complete yet. Everything I've tried is either unparseable or results in a value. =IF([Actual Finish]@row > [Planned Finish]@row, "Late", IF([Actual Finish]@row = [Planned Finish]@row,…