Syntax
ANCESTORS([reference])
- reference—[optional]The cell and column for which you want the ancestors. If no cell is specified, the function references the ancestors of the current cell in the column where the formula is located.
Sample usage
COUNT(ANCESTORS([Task Name]@row))
Usage notes
The sample usage formula above produces a number indicating how far down in the hierarchy the current row is positioned. This value can drive conditional formatting by highlighting specific hierarchy levels using different fill colors or font sizes or creating nested formulas that perform different calculations for top-level versus indented rows.
ANCESTORS allows you to target values from a single column on rows above a selected cell based on the sequence of a hierarchy. It traces a path from the top-level hierarchy to the current indentation level. It doesn’t include rows at the same hierarchy level when multiple rows are indented.
You can’t use ANCESTORS independently. You must use them as the range value for another function, such as SUM, COUNT, JOIN, or INDEX. Use the values collected by ANCESTORS to perform calculations or display one or more values from within the assembled list.
ANCESTORS, by default, refers to rows related to the cell where the formula is located. You can specify another cell within the sheet by adding it as the reference value.
- ANCESTORS([Column Name]@row) - Ancestor values in a different column from the formula, based on the current row
- ANCESTORS([Column Name]10) - Ancestor values in a different column from the formula, based on the specified row
A cell reference is not required when the function refers to values from the same column in which the formula is located.
Examples
This example references the following sheet information:
Based on the table above, here are some examples of using ANCESTORS in a sheet:
| Formula | Description | Result(s) |
|---|---|---|
| =COUNT(ANCESTORS()) | Counts the number of ancestors for the cell containing the formula This formula is within each row of the Level column. | Row one: 0 Row two & three: 1 Row four through six: 2 |
| =INDEX(ANCESTORS([Item Number]2), 2) + " - " + "Design Phase" | Returns the ancestor of the value in row 2 of the Item Number column, then adds the values - Design Phase to the result | MF01 - Design Phase |
| =INDEX(ANCESTORS([Project Number]@row), 1) + " - " + Description@row | Returns the top-level ancestor value for “Project Number” and adds it to the current task Description.
This creates a high-level reference value that can be included in reports to indicate which project the specific task is associated with. | MF01 - Design |
| =JOIN(ANCESTORS(Description@row), " - ") | Returns a list of all ancestor values for Description as a text string
This creates a detailed reference value that can be included in reports to indicate all project phases associated with the specific task. | Formal Dress Shirt - Assembly - Add Buttons |
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function you can practice working with in real time and examples of commonly used and advanced formulas.
Consider using the Project with Work Breakdown Schedule (WBS) template from our Template gallery to access a template with the ANCESTORS function in place.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.