Can Someone Share a Working WBS Formula?

Desi

I'm having issues with WBS formlas.

I have 3 columns:

RowID

Level, and

WBS

I pulled formulas from a Smartsheet template online (from a forum) but it's not working properly.

The first row is the Project Title

The second row starts Phase 0 - Project Initiation — it starts with 1,

Numbering structure is 1, 1.1, 1.2, etc.

Phase 1 begins with 2 — 2.1, 2.2, etc.

Could someone help with the correct formula?

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Georgie

Hi @Desi,

Have you had a look at the template gallery? There’s a template set there named “Project with Work Breakdown Schedule (WBS)” - I’d recommend clicking this link to download the template set, and having a look at the Task Tracker sheet to see how the WBS formula has been set up. That sheet includes a “Skip WBS” checkbox column which you might want to add to your sheet so that you can exclude rows from WBS, but you don’t have to do this.

What you will need is a formula within your “Level” column that automatically calculates the hierarchy level of each row. In the template sheet, the formula used in the Level column is:

=IF(NOT([Skip WBS]@row), MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Ancestors:Ancestors, Ancestors@row, [Skip WBS]:[Skip WBS], false), 0))

However, if you don’t want to add the Skip WBS column, you could use the following formula:

=IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Ancestors:Ancestors, Ancestors@row, [Skip WBS]:[Skip WBS], false), 0), "")

Make sure whichever one you use, you right-click the cell and select Convert to Column Formula.

Once your Level column is set up correctly, you can then use the following formula in the WBS column (and convert it to a column formula):

=UPPER(IF(COUNT(ANCESTORS()) = 0, Level@row, JOIN(COLLECT(ANCESTORS(Level@row), ANCESTORS(Level@row), NOT(ISBLANK(@cell ))), ".") + "." + Level@row))

Again, the formula in the template differs because of the Skip WBS column - so if you do add that column, you can use this formula, just like in the template sheet:

=IF(NOT([Skip WBS]@row), UPPER(IF(COUNT(ANCESTORS()) = 0, Level@row, JOIN(COLLECT(ANCESTORS(Level@row), ANCESTORS(Level@row), NOT(ISBLANK(@cell ))), ".") + "." + Level@row)))

I tested this with my sheet set up with phases starting from 0, just like in your screenshot, and confirmed that the WBS showed as you requested - with WBS starting from 1 for Phase 0, from 2 for Phase 1, and so on. You can see this in my test sheet below:

Does that work for you?

Georgie

All comments

florian.zbinden7

hello @Desi

I have one formula working with the same columns as you have, and you setup looks the same as mine, so this should work:

the level column has this formula

and the row id is the standard one:

{4522390F-020B-4470-8B12-18FCA13B53C7}.png

hope this helps,

cheers

Florian

Desi

Thanks. Let me give it a try.

desherr

Hi @florian.zbinden7 I tried it and it doesn't show the levels, 1.1, 1.2, etc. What am I missing?

desherr

The Row ID screenshot you show used auto numbering. When I switch mine from a formula to auto number, that doesn't fix it, unless I need to adjust numerical places or the starting number. Even entering "1" doesn't fix the WBS sequence.

Desi

desherr

Maybe it's the WBS formula:

=IF(Level@row = 1, COUNTIF(Level$1:Level@row, 1), PARENT() + COUNTIFS(Level$1:Level@row, Level@row, RowID$1:RowID@row, >PARENT(RowID@row)))

Is there a tweak to recognize and number the hierarchies based on the parent rows: 1, 1.1, 1.2, 1.2.1, etc.?

Or do I need to add a new helper column?

Desi

Also, the RowID, when I change it to auto number, starts at a different number (does this look correct?)

florian.zbinden7

you miss the + "." + in the middle of my formula

florian.zbinden7

this is the entire formula, easier to copy

=IF(Level@row = 1, COUNTIF(Level$1:Level@row, 1), PARENT() + "." + COUNTIFS(Level$1:Level@row, Level@row, [Row ID]$1:[Row ID]@row, >PARENT([Row ID]@row)))

Desi

Almost there.

It still doesn't seem to recognize the Phases. I have about 5 phases

Phase 0 - should start numbering at 1

Phase 1 - should start at 2, and so on

1
1.1
1.2
2
2.1
2.1.1

Do I need another column?
I think the title row would also be "0"?

Desi

Hoping an expert can share with me how to order the WBS numbers appropriately. Does it require a tweak to the formula? Or another helper column of some sort? Thanks.

Georgie

Hi @Desi,

What you will need is a formula within your “Level” column that automatically calculates the hierarchy level of each row. In the template sheet, the formula used in the Level column is:

=IF(NOT([Skip WBS]@row), MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Ancestors:Ancestors, Ancestors@row, [Skip WBS]:[Skip WBS], false), 0))

However, if you don’t want to add the Skip WBS column, you could use the following formula:

=IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Ancestors:Ancestors, Ancestors@row, [Skip WBS]:[Skip WBS], false), 0), "")

Make sure whichever one you use, you right-click the cell and select Convert to Column Formula.

Once your Level column is set up correctly, you can then use the following formula in the WBS column (and convert it to a column formula):

=UPPER(IF(COUNT(ANCESTORS()) = 0, Level@row, JOIN(COLLECT(ANCESTORS(Level@row), ANCESTORS(Level@row), NOT(ISBLANK(@cell ))), ".") + "." + Level@row))

Again, the formula in the template differs because of the Skip WBS column - so if you do add that column, you can use this formula, just like in the template sheet:

=IF(NOT([Skip WBS]@row), UPPER(IF(COUNT(ANCESTORS()) = 0, Level@row, JOIN(COLLECT(ANCESTORS(Level@row), ANCESTORS(Level@row), NOT(ISBLANK(@cell ))), ".") + "." + Level@row)))

Does that work for you?

Georgie

Desi

Thank you so much @Georgie. I am using a SmartsheetGov account, so I am unable to use the templates provided in the commercial Smartsheet solution - the information doesn't transfer over or at least I can't capture it appropriately using the free trial of the commercial Smartsheet.

I will try these formulas. I figured there was something in the formulas I was missing to be able to number the phases correctly.

Thanks again. I will try these.

ManuHouse

https://community.smartsheet.com/discussion/comment/480882#Comment_480882

Hi @florian.zbinden7, I am using your formula in one of my sheets and it fixed the challenge for me. So first of all - thanks a lot :)

However, I am wondering if it is possible to add a "." after the single digits (highest hierarchy level); so e.g. that it is "1." instead of "1".

If I simply add the " + "." " to the formula, the format obviously isn't correct for deeper hierarchy levels (e.g. it's 1..1. instead of 1.1 then).

Do you have a solution for this?

Would be highly aprreciated!

Best regards

florian.zbinden7

@ManuHouse

To be honest, I'm not able to help you on this one ; I tried but this did not work.

@Georgie can you help on that matter ?

Georgie

Hi @ManuHouse,

I tried the following formula out in my test sheet and got it working as I understand you intend it to. Give this a try:

=IF(NOT([Skip WBS]@row ), UPPER(IF(COUNT(ANCESTORS()) = 0, IF(LEN([Level Code]@row ) = 1, [Level Code]@row + ".", [Level Code]@row ), IF(LEN(JOIN(COLLECT(ANCESTORS([Level Code]@row ), ANCESTORS([Level Code]@row ), NOT(ISBLANK(@cell ))), ".") + "." + [Level Code]@row ) = 1, JOIN(COLLECT(ANCESTORS([Level Code]@row ), ANCESTORS([Level Code]@row ), NOT(ISBLANK(@cell ))), ".") + "." + [Level Code]@row + ".", JOIN(COLLECT(ANCESTORS([Level Code]@row ), ANCESTORS([Level Code]@row ), NOT(ISBLANK(@cell ))), ".") + "." + [Level Code]@row ))))

Does that work for you?

Georgie

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.