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.
Adrian Mandile
CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions -
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!