ANCESTORS Function

Used within another function to reference all of the ancestors in a cell’s hierarchy
Sample Usage
COUNT(ANCESTORS([Task Name]4))
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.
Examples

This example references the following sheet information:

Row #

Project

LEVEL

Item Number

Cost Per Unit

Due Date

1

- Men's Formal Button Down

0

MF01

$38.75

02/27/19

2

  MF01 - Design Phase

1

DP

$25.00

02/19/19

3

-- MF01 - Assembly

1

A0

$13.75

02/27/19

4

    Cutout Shirts

2

A001

$10.00

02/22/19

5

    Sew Shirt

2

A002

$2.50

02/26/19

6

    Add Buttons

2

A003

$1.25

02/26/19

 

Given the table above, here are some examples of using ANCESTOR in a sheet:

Formula

Description

Result(s)

=COUNT(ANCESTORS())

Counts the amount 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

=SUM(ANCESTORS([Cost Per Unit]5))

Sums the ancestors for row 5 in the Cost Per Unit column

 

It has two ancestor rows (rows 3 and 1).  

$52.50

=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

Still need help? 

Use the Smartsheet Formula Template to view examples and practice working with functions in real-time.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community