Report showing particular sheet Parent+Children rows

copperwr
copperwr
edited 02/20/24 in Formulas and Functions

I want to populate a Report with a particular Parent row (level 6 indent) + it's children.

The sheet is a very large schedule running to more than 1000 rows and with 7 levels of indent and counting.

This is something I need to do on a regular basis - is there some hack to achieving this?!

Thanks,

Robert

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭

    @copperwr

    You could create a helper column for each incident level and use this formula in each level, adjusting it slightly.

    This above would be for level 2.

    Level 3 =IF(COUNT(ANCESTORS()) = 2, 3, [Incident Level]@row)

    and so on.

    This assumes you have a top level row and every row under that is indented.

    I use the Incident Level column to test the accuracy of my formula. You can have an incident level column with the formula =COUNT(ANCESTORS()) or you can adjust my above formula to say:

    =IF(COUNT(ANCESTORS()) = 1, 2, COUNT(ANCESTORS()))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!