WBS Formula Question

Erik B

I was hoping to get some help with a question regarding the WBS template from SmartSheet

https://www.smartsheet.com/marketplace/templates/work-breakdown-structure-wbs?_gl=1*1vh9x02*_ga*MTE3MzI3NTI5Mi4xNjU0MDk3NzY4*_ga_ZYH7XNXMZK*MTY2NTQyMDE3My40Ni4xLjE2NjU0MjAzNDQuMjAuMC4w&_ga=2.127570445.1837829340.1665420174-1173275292.1654097768

I found this template very helpful, and I would like to use it for my projects, but I need a little assistance with modifying the formula. I am not sure if this same setup can be used when the project tasks don't start on the first row. For example, my tasks begin on row 28 because we use the first 27 rows for summary information, but also, I indent the first task in order to be able to track up the overall project health and completed %. Any advice on how I can make a modification to this trick to have the first task on line 28 begin with 1, and so on would be appreciated.

Michael Culley

If you right click a cell in the WBS column and click edit column formula you can do something like this:

=IF(VALUE(IF(Prefix@row <> "", Prefix@row, "") + Suffix@row) - 3 < 0, 0, VALUE(IF(Prefix@row <> "", Prefix@row, "") + Suffix@row) - 2)

Basically that makes the first 3 PARENTS equal to 0, then the rest count as normal.

Not sure if that's what you're looking for.

Michael Culley

Also if you'd rather it show as blank instead of 0, use this:

=IF(VALUE(IF(Prefix@row <> "", Prefix@row, "") + Suffix@row) - 3 < 0, "", VALUE(IF(Prefix@row <> "", Prefix@row, "") + Suffix@row) - 2)

Erik B

Hi Michael,

Thank you for the suggestion, I think the second scenario is closer to what I was trying to achieve. I tried using your formula and it did start the Parent number at row 24 this time, which is closer, but hoping to start it at row 29. For the children it's giving me #INVALID VALUE as the result. Any ideas what I might be doing wrong?

Michael Culley

I think it's giving you invalid value because you have multiple children for 1 parent. I thought it was setup to only have 1 parent from what I could see.

You can also adjust my formula by changing the minus numbers. So instead of -3 do -5 and instead of -2 do -3

Erik B

Hmmm.. I tried that but it's becoming a bit of a mess now. Doesn't seem to be working for me. Would you mind taking a look at my screen capture to make sure I did it right?