For organizations looking to adopt Smartsheet, partnering with a Smartsheet Platinum Partner offers several advantages:

In any project, certain tasks are more critical than others in determining the project’s overall timeline. The ISCRITICAL formula in Smartsheet evaluates each task’s criticality based on its dependencies and the effect any delays might have on the project’s completion date.

By identifying critical tasks using the ISCRITICAL formula, project managers can focus their efforts on ensuring these activities stay on track, reducing the risk of project delays. This knowledge can guide project managers in prioritizing resources and allocating them effectively to meet crucial deadlines.

The real power of the ANCESTORS formula lies in its ability to work in tandem with other functions, allowing users to perform various operations based on hierarchical relationships.

COUNT Function

With the COUNT function, you can determine the number of ancestors a row has. This information can be invaluable for automating row formatting, where specific formatting is applied based on the number of ancestors a row possesses. For example, epic rows might have distinct background and font colors, while stories have their own formatting, and so on.

SUM Function

The SUM function, when used with ANCESTORS, can aggregate numerical values from ancestor rows to the child row. This aggregate value can then be employed for filtering, conditional formatting, decision making, and more. For instance, you can use the SUM function to combine dollar amounts from all ancestor rows to a particular task or subtask, making it easier to manage budgets and resource allocations.

INDEX Function

When combined with ANCESTORS, the INDEX function becomes a powerful tool to extract specific information from ancestor rows. For example, you could use INDEX to pull the phase name corresponding to each row. This information could stand alone or be concatenated with another value to create a unique identifier, which can then be used in reports or other parts of the project management process.

JOIN Function

The JOIN function, when used with ANCESTORS, allows you to collect all ancestor values and combine them into a single field, separated by a delimiter of your choosing. This can be particularly helpful when you want to capture “breadcrumbs” or a trail that gives insights into the hierarchical position of a particular task or item. Having this information readily available in the row itself enhances understanding and navigation within the project management sheet.

COUNT Function

When used with the CHILDREN formula, COUNT enables users to determine the number of child rows under a specific parent. For example, if a phase named “Plan” has 5 tasks directly indented under it, the COUNT function would return 5. Any subtasks under these tasks would also be counted as children for the corresponding task. Additionally, COUNTIF can be used to narrow down the count to only children that meet certain criteria. For instance, you could count only the tasks that are not closed for a particular phase.

SUM Function

With the SUM function and the CHILDREN formula, users can aggregate numerical values associated with child rows to their respective parent rows. This aggregation is useful when tasks have cost or other numerical data associated with them. By using the SUM function, you can roll up all the task costs to the parent row, providing a clear overview of the total project cost. Similarly, SUMIF can be employed to sum only the children rows that meet specific criteria. For example, you could sum up only the closed tasks to calculate the total cost accrued to date.

MAX Function

When combined with the CHILDREN formula, the MAX function returns the highest value within a range of cells corresponding to child rows. This can be particularly helpful when you want to identify the most expensive tasks in a project. Alternatively, when working with a date range, MAX can be used to find the date of the last task scheduled to be completed, giving you a clear picture of the project timeline.

IF Function

By using the PARENT formula inside an IF function, users can create conditional expressions based on the parent’s value. For example, if you have a Project Plan sheet organized into phases and tasks, you may want a formula to return a value only if the parent is “Plan” (indicating that the task is a child of the “Plan” phase). In this scenario, you can use the IF function and nest the PARENT function, like this: “=IF(PARENT([Task Name]@row) = ‘Plan’, ‘Value if True’, ‘Value if False’)”. This formula will display “Value if True” if the parent is “Plan,” otherwise, it will display “Value if False.”

Concatenation

Concatenation is the process of combining multiple strings of text into a single string. In Smartsheet, concatenation is represented by the “+” (plus) sign, rather than the “&” (ampersand) used in Excel. The PARENT formula can be used within a concatenation to build more informative output. For example, you might want to create a formula that combines the parent’s task name with the child task name to form a complete task identifier. You can achieve this with the formula: “=PARENT([Task Name]@row) + ‘ – ‘ + [Task Name]@row”.