Creating an Outline value

I am trying to create an Excel export from my SmartSheet project plan for the purposes of importing select tasks into an internal PPM solution. One issue I have is the lack of an Outline field, just like we have in MSProject. Has anyone tried and succeeded to create a column within SmartSheet that can automatically create such values?

Answers

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭

    Hi @SSK_REGN,

    I think what you are after can be achieved by creating a helper column – I'd name it "Level" – then add a column formula in it that references the primary column (e.g. Project Tasks), like this:

    =COUNT(ANCESTORS([primary column]@row))+1

    If it is a top-level row, then it won't have any ancestors (i.e. =0) and the 'Level' formula will equal 1. And so on, for subsequent levels.

    With a little modification to this formula, so that it first checks if a row has no CHILDREN() tasks, and leaves the 'Level' column blank (""), this can become a very useful column for all sorts of other columns that require a conditional formula - e.g. if it's a summary row use an =SUM() formula, or else (if blank) for use another formula.

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

  • SSK_REGN
    SSK_REGN ✭✭✭✭

    Hello Adrian,

    Thanks for the tip. I have already tried this to get the "level". However, the real challenge is to generate the Outline in the format equivalent to that of MSProject; See the table below for illustration:

    Any thoughts on how to achieve this would be greatly appreciated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!