2

I need to be able to create hierarchy in the rows to separate tasks from sub tasks and sub sub tasks. Basically the task is Level 1 the sub task is level 2 sub sub task is level 3 and sub sub sub task is level 4.

enter image description here

So the cell A1 should contain formula that when copied to cell G1 should strip value of the cell F1 and then +1 so that the value of the cell G1 will become 2. The cell B1 should contain formula that will take value of the cell A1 and add .1 so that the value becomes 1.1 and if copied further down will increase the value by .1 so that the next cell is 1.2, 1.3 and so on. The rest of the steps for level 3 and level 4 should be almost similar the formula in level 2.

I am trying to do this without VBA just simple formulas that can be placed as template rows in my spreadsheet and can be copied to any place of the spreadsheet when needed.

Improve this question
5
  • @pnuts I have just updated my question with image as requested. Thanks for suggestion Commented Dec 30, 2014 at 20:39
  • The F1 at this point would not matter at all, main part for me is to be able to create the hierarchy as shown on the image. I was able to complete the first step, but at the moment sitting on the second and cannot get my head over it Commented Dec 30, 2014 at 20:48
  • 1
    What I am intended to do is to store everything in the template rows so that when either all rows are copied or one of the is inserted in between to create additional level then formula should automatically pick value of the row above and make necessary adjustments to it depending on the level of the row. Commented Dec 30, 2014 at 20:55
  • Depending on the Level, if you try to insert Level 1 then the value will be 2 if level 2 then 1.4 if level 3 then 1.2.3 and if level 4 then value is going to be 1.3.1 as there is no level 3 above it. Commented Dec 30, 2014 at 21:05
  • Column B is there only to show the levels to make it easy to understand what I meant in my example in reality there I am going to put my tasks in column B. So what matters is what in Column A Commented Dec 30, 2014 at 21:10

2 Answers 2

Reset to default
5

Ok, first of check this article out here, you should be able to find everything you need to create what you need. Now here is all formulas per every level in your example

Level 1

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A1,-1,0,1,1),".",""))),1,IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))),VALUE(OFFSET(A10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(A1,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))-1))+1))

Level 2

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A2,-1,0,1,1),".",""))),"0.1",IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))),OFFSET(A2,-1,0,1,1)&".1",LEFT(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1)))&IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))),VALUE(RIGHT(OFFSET(A2,-1,0,1,1),LEN(OFFSET(A2,-1,0,1,1))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))))+1,VALUE(MID(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))+1,(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))-1)))+1)))

Level 3

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A3,-1,0,1,1),".",""))),"0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))),OFFSET(A3,-1,0,1,1)&".1",LEFT(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))),VALUE(RIGHT(OFFSET(A3,-1,0,1,1),LEN(OFFSET(A3,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))))+1,VALUE(MID(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))+1,(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))-1)))+1)))

Level 4

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A4,-1,0,1,1),".",""))),"0.0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))),OFFSET(A4,-1,0,1,1)&".1",LEFT(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))),VALUE(RIGHT(OFFSET(A4,-1,0,1,1),LEN(OFFSET(A4,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))))+1,VALUE(MID(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))+1,(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))-1)))+1)))

Of course there may be some other way, but this works too.

Let me know if you have problem with this.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

OK, thanks I will give it a try and will get back to you in few minutes
Thanks UXML your answer works perfectly for me. Thanks a lot and Happy New Year!!!
2

I had this problem too. I solved it like this:

  1. I focused on the dots. Each dot is a hierarchy. At cell A2, to find hierarchy I used this formula. 

    =LEN(c2)-LEN(SUBSTITUTE(c2;".";""))

    The C column is where i have the tree. After this formula populate the whole column. For each dot found it increases/decreases one number, so you have the levels set.

  2. For 4 levels I have made this formula, but you can increase it accordingly to your needs: In cell B2 set this:

    =IF(A2=4;"level4"&C2&" "&D2; IF(A2=3;"level3"&C2&" "&D2; IF(A2=2;"level2"&C2&" "&D2; IF( A2=1;"level1"&C2&" "&D2;IF(A2=0;""&C2&" "&D2)))))

    The cell D2 is the name the hierarchy refers to: eg. C2= 1.1.1 D2= Arthur

This web form "ate" all my spaces so I substituted them by level4,level3,level2,level1,level0. To achieve the tree style please substitute each level0,1,2,3,4, with 5 spaces multiplied by the level number.

After this, populate the whole column. I used spaces so it can show clearly the hierarchy.

It worked for me. Hope it is useful to you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.