Syntax
ANCESTORS([reference])
- reference—[optional]The cell for which you want the ancestors. If no cell is specified, the function references the ancestors of the current cell.
Sample usage
COUNT(ANCESTORS([Task Name]4))
Examples
This example references the following sheet information:
Row # | Project | Level | Item Number | Cost Per Unit | Due Date |
---|---|---|---|---|---|
Row # 1 | Project Men's Formal Button Down | Level 0 | Item Number MF01 | Cost Per Unit $38.75 | Due Date 02/27/24 |
Row # 2 | Project MF01 - Design Phase | Level 1 | Item Number DP | Cost Per Unit $25.00 | Due Date 02/19/24 |
Row # 3 | Project MF01 - Assembly | Level 1 | Item Number A0 | Cost Per Unit $13.75 | Due Date 02/27/24 |
Row # 4 | Project Cutout Shirts | Level 2 | Item Number A001 | Cost Per Unit $10.00 | Due Date 02/22/24 |
Row # 5 | Project Sew Shirt | Level 2 | Item Number A002 | Cost Per Unit $2.50 | Due Date 02/26/24 |
Row # 6 | Project Add Buttons | Level 2 | Item Number A003 | Cost Per Unit $1.25 | Due Date 02/26/24 |
Given the table above, here are some examples of using ANCESTORS in a sheet:
Formula | Description | Result(s) |
---|---|---|
Formula =COUNT(ANCESTORS()) | Description Counts the number of ancestors for the cell containing the formula This formula is within each row of the Level column. | Result(s) Row one: 0 Row two & three: 1 Row four through six: 2 |
Formula =SUM(ANCESTORS([Cost Per Unit]5)) | Description Sums the ancestors for row 5 in the Cost Per Unit column It has two ancestor rows (rows 3 and 1). | Result(s) $52.50 |
Formula =INDEX(ANCESTORS([Item Number]2), 2) + " - " + "Design Phase" | Description Returns the ancestor of the value in row 2 of the Item Number column, then adds the values - Design Phase to the result | Result(s) MF01 - Design Phase |
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.