Ancestor 2 to autofill with Ancestor 0 value in primary column


I am in the process of creating an assignment sheet for staff. There are a few goals in the sheet.

  1. Card View by associated projects (ideally be able to move them/reassign them with ease)
  2. Card View by staff member
  3. Grid View by department. Staff to add percentage of time projected to spend per project separated by cost center. Finance wants rolled up % of time per staff per cost center for allocate their salary to the correct department.
  4. See what projects are unassigned and easily assign them

Current challenge- would like staff name from ancestor 0 to autofill to ancestor 2 in primary column so when looking in card view by project, untitled will be replaced with staff name. When using =PARENT(), it is pulling the department name of Ancestor 1 into ancestor 2.

And since primary column, making an column formula isn't working.

Any help to solving is greatly appreciated!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Brandee Pak

    The ANCESTOR function will be able to provide the reference to whatever level of ancestors you prefer.

    =INDEX(ANCESTORS(Task@row), 1)

    However, unless you are doing your initial [Task] Parent/Grandparent input into another column, you will not be to create a column formula. If you decide you could enter the Task value in a different cell (for the Parent row entry), you could easily have a column formula that would correctly and automatically pull in the appropriate Parent/Grandparent/Child data into the Task column to look exactly like you want it to look.


  • Brandee Pak

    Thank you!

    How have you seen others handling the use of Card View and the Primary Column?

    I already have a helper column in place to pull down the staff assignments if a row is added. However, trying to avoid having the 'untitled' appears if the Primary column is empty.

    I added the formula you provided to what is there and it works great. Even autofills the formula if enter between two lines. But would like to make it as easy as possible for the staff that will be adding in rows of assignments that are not as familiar with smartsheet.

    Is it best practice to simply add the information into the Primary column manually to avoid the 'Untiled' appearing?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!