JOIN function for child row


Hi everyone,

I'm currently working on a way to leverage formulas and row hierarches to create a ROW ID column. Essentially what I would like to happen for children rows to have the same "item ID" as their ancestorial row.

This is what I have so far for the parent and child row formulas (currently seperate)

Parent Formula:=IF(Level@row = "1", JOIN([Ticket #]@row:Category@row, "-"))

Child Formula:=IF(Level@row > "1", ANCESTORS(JOIN([Ticket #]@row:Category@row, "-")))

I have also attached a screenshot, The first three columns for children rows will be blank due to the parent row being pulled in from another sheet.

Any help would be appreciated, thanks!


  • Kelly Drake
    Kelly Drake Overachievers Alumni

    I don't see a Level column so that maybe part of the issue.... Try this...

    =IF(COUNT(ANCESTORS([Ticket #]@row)) = 0, [Ticket #]@row, PARENT(@cell))

    You could then turn this into a column formula rather than having to change the formula based on if it's a parent row or not.

    My solutions usually data in all cells so I'm not 100% confident the @cell part will work so you'll have to try. If the above doesn't work you can try

    =IF(COUNT(ANCESTORS([Ticket #]@row)) = 0, [Ticket #]@row, PARENT([Ticket #]@row))

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • nlarson_PMC
    nlarson_PMC ✭✭
    edited 04/29/22

    Hey Kelly

    There is a Level column, it just at the other end and so wasn't in the picture.

    But your formula's might work better thought than what I was trying. I'll give them a shot and let you know.

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!