-
How can I get parent row roll up of child tasks health status
Hi - I am using health status which is determined by project status which is in turn determined by a planned vs actual task progress. I need the the health status to roll up to the parent row since I can't get an accurate weighted planned % complete in the parent row. I am using the following formula in child tasks health…
-
How to use Nested IF AND/OR Formulas?
I want to compare two columns Health and Date and populate the column for update required or not required. Conditions are If the date is in the past and Health is Blue - Update Not Required If the date is the past and Health is either Yellow, Green OR Red - Update required If the date is in the future and Health is either…
-
Searching for a value in a multiselect column
I probably should be going to bed, but I'm going nuts trying to figure out one last formula. I have a sheet with a multiselect column named "Project Type". The column has the following values: Revenue Opportunity Revenue Loss Avoidance Risk Compliance Cost Saving I am trying to create a formula along the lines of: IF…
-
Combining multiple views of a project plan in one
In our previous solution, we had five project plans for users to choose from. This caused reporting issues as our milestones did not align between project plans. Has anyone combined project plans into one template and allowed the user to see the appropriate tasks for their chosen project plan by perhaps a filter? Example,…
-
Count occurrences of each variable of a multi-dropdown list
Hi, I'm building a lease administration tool to help negotiate some leases. Each row (lease) has several stages (preliminary, negotiating, at lease, executed lease, open). I want to create a dashboard metric to show how many rows are at a particular stage: Preliminary - 5; Negotiating - 3; At Lease - 7, etc... What would I…
-
Date Range Conflict - Vacation Request Sheet
Hello, I've seen quite a few questions for date range overlap, but couldn't find the solution. The closest I found was the one below, but it was referencing another sheet, not the same sheet I believe, and not sure a simple edit to that formula will make it work for me. Form requests will populate new rows of all time off…
-
Saved Filters Issue Date Type Column
I'm running into a very odd issue with consistent filtering on a date column thats being generated from a formula: We have a column labeled "Filter date" that is set to Date (Restrict to dates only not checked) which has the following formula to trim off the date from a 2ndary column that is a drop down: =IF([Date…
-
Count Responses for the Same Answer, for Multiple Persons
I created a survey and used the following formula to count repsonses; =COUNTIFS({2020 IS Employee Engagement Survey Range 3}, "Vin Miliano", {2020 IS Employee Engagement Survey Range 1}, "4 - Agree") The formula works fine for Vin, as his number of responses is 2. I also have another user, Tim, and his response is 2. How…
-
Create SUMIFS formula
I need help creating a formula that will show the sum of the Forecast Amount for any Opportunity that has one of these Sales Stages: 1 - Prospect 2 - Assessment 3 - Proposal 4 - Contracts If it's easier to write one that includes "everything except", these would be the ones to exclude: Closed Won Closed Lost All I come up…
-
Start and end date columns advanced by one year
Hi all, I'm trying to flip ALL dates in the start column ahead by 1 year and ALL dates in the finish column ahead by one year. We're utilizing Smartsheet as our critical path platform so this is something we need to do year over year and manually updating ~1000 rows is not realistic. An exact date is not the most important…