-
Project Intake Sheet looks correct, Project Metadata and Project Plan missing reference
Hello all, I'm new to SmartSheet so still learning. I'm using the PMO template which makes sense so far, as per the instructions. If I understand the functionality right, information feeds through from Project Intake Sheet to the Metadata and Plan sheets in the specific project folder (ie. Project Name feeds through to the…
-
How to combine if statements utilizing information in multiple columns
Hello, I currently have the following formula to automatically have a color coded dot assigned in a column depending on the due date. =IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))) Can you help me add to this same formula an if statement that…
-
Count unique projects
I want to count the number of unique projects under a particular workstream. For example, a workstream might have three projects under it, but each of those projects might have three people assigned to it. That's 9 occurrences in that workstream, but only three unique projects. How can I count the unique projects under…
-
Help with SUMIF
Hello, I am hoping to get some guidance on constructing a formula. I am trying to calculate the number of days each person have been allocated for projects. This will help me in understanding monthly/annual labour allocation and available free time. I have made column for each person on the right and entered the results I…
-
How to use Conditional Formatting for entering text when a condition is met?
I am creating Project Plan in Smartsheet. If % Complete = 100% I want Status column to be updated with text = "Complete". If % Complete < 100%, I want Status column to be updated with text = "In Progress". If % Complete = 0% I want Status column to be updated with text = "Not Started".
-
I am trying to create Workflows based on varying frequency and day row is added.
I have a Smartsheet that gets updated with new data every Monday. As soon as a row is added, the project manager gets a notification to set the "Reporting Frequency". This can be Weekly, Bi-Weekly, or Monthly. The first Thursday after the row is added, a Buyer should receive a workflow that requests updates. Then the Buyer…
-
Countifs with criteria on 2 different ranges
hello, I have formulas in a metrics sheet looking at information in a second sheet. I want to count the number of items in the second sheet that meet both a criterion of a checkbox being checked in one column (SS range 2) and the value in another column (SS range 3) being between 2 values (in this case, between 0 and the…
-
VLOOKUP with Multiple Criteria or should use INDEX or COLLECT?
Hi! I'm trying to do a multiple criteria look up and I'm not sure I'm going about it with the correct formula. I have a Project ID referenced on a project profile, then the formula needs to find that project and display the data, easy with a vlookup. Here's where it gets fun, I need it to look at the next unique time entry…
-
Ranking without Duplicating Ranks
I used the RANKEQ function for the first time recently and realized that any identical values will have the same rank. It will skip however many duplicates you had in the ranking numbers, so you may have a ranking scheme that looks like 1, 2, 3, 3, 3, 6, 7, 7, 9, 9. This may be useful in some situations but it is actually…
-
Net Days Between Due Dates, Referencing Current Row and Row Above
I am hoping to identify a duration between dates on my sheet but would like to reference the current row and row above in the formula. I created two helped columns to identify Row # - auto number column "Auto" and a "Row #" column with the following formula =MATCH(Auto@row, Auto:Auto, 0).