Creating an Outline value

SSK_REGN

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?

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.

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.