I need a modification on an IF THEN statmenet

Jewelle W

I have a column in my grid that generates the Task number automatically for Level 2 and all of the children below it. IE if I choose the drop down "Task 1" for a row that is level 2 then that row and all children of that row will show "Task 2". I did that using this formula:

=IF(Level@row = 2, LEFT([Task #]@row, 7), IFERROR(LEFT(IF(Level@row < 3, "", INDEX(ANCESTORS([Task #]@row), 2)), 7), ""))

See screenshot:

I need an additional condition added so that for rows that the Level is 3 then an "a", "b", or "c" is added sequencially based on the number of children

The end result I am looking for is for the screenshot above to say "Task 4" in the "Task#" column and then the Task Number (2 Test) column then says "Task 4" for row 2, "Task 4a" for row 3, "Task 4B" for row 4 etc. Ideally I would like for this to be a column formula if possible.

I attempted this formula:

=IF(Level@row = 2, LEFT([Task #]@row, 7), IFERROR(LEFT(IF(Level@row < 2, "", INDEX(PARENT([Task #]@row, AND("a"), 3)), 7), ""))

but it comes back "#UNPARSABLE" and even if worked I don't think it would be able to be converted to a column formula do do what I need.

Is this something Smartsheet can do? Am I on the right track with this formula or does it need to be reworked completely?

prime_nathaniel

Jewelle W

Tried but no luck, I actually had a customer that we did auto child numbering for but we only went 1 level down not 2 and that is where the issue seems to be.

So you can see in my example below. You need ALOT of helper columns to pull this off.

1. Level determines ancestory + 1
2. Index is the index of the row in the sheet
3. Parent Item Sequence, is the sequential id of each parent row in order (won't work if you resort so be aware of always sorting by some sequential row id)
4. Child item sequence this is where it is breaking, I am trying to run a collection of rows that just belong to the parent and then performing an index sequence on them so they number 1, 2, n then for the next parent start back at 1 but that seems to not be working.
5. If 4 is fixed, then all you have to do is concatenate the values together with a . or something between them and it would result in 1.1.1 format or whatever you were looking for.

Perhaps someone else has a multi hierarchy example they still have access to. I can't remember what customer I saw it on.