Formula Help Needed: To populate unlimited level of children row with Parent data

Question

With reference to the following screenshot, we want to Populate the EP Number for all children under Level 1, regardless of the number of hierarchy with the EP No at Level 1.

At this moment, with =IF(COUNT(CHILDREN()) > 0, [EP No]@row, PARENT([EP No]@row)), row 4 onwards are empty.

What formula can I use to populate the EP Number column, so that if it is Level 1, it will reflect the EP No at row, if it's more than Level 2, it should reflect the EP No as stated in Level 1?

In the screenshot above, all the rows under Executive should reflect ABC.

Any assistance would be appreciated.  Thank you.

Nick Korna · Answer

@Vivien Chong, if you've still got the level column then yes, we can amend the formula slightly to get round this.

If you call your "testing 1" type task level 0, then:

=IF(Level@row <= 1, [Ep Number]@row, PARENT([Ep No]@row))

Sample:

Obviously if your level number system is slightly different then you will need to amend the number in the formula but hopefully this gives you the gist of what to do - let me know if there are any other issues. 🙂

Vivien Chong · Answer

Hi @Nick Korna thanks for your prompt reply.

I realised I have missed out the 1st row... and it became like this. 😅

Do you have any idea how we can refine this formula?

Appreciate your help.  Thanks.

Nick Korna · Answer

Hi @Vivien Chong,

You should be able to use a formula like this in your Ep No column and only need to input the Ep Number once to have it populate all children of that task:

=IF(ISBLANK(PARENT([Ep No]@row)), [Ep Number]@row, PARENT([Ep No]@row))

Sample:

Hope this helps, but if you've any problems/questions then let us know.