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
-
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.
-
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
Categories
Check out the Formula Handbook template!