JOIN function for child row

nlarson_PMC · 04/29/22

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 · 04/29/22

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))

nlarson_PMC · 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!

JOIN function for child row

Answers

Help Article Resources

Categories