excel - First used cell above address -

i'm creating budget in excel , therefore have categories , subcategories. subcategories intended 1 cell compared respective parent category. subcategories of course can have subcategories on own.

in column next "category tree" want print "category path".

let's have following tree of categories:

i want "category path" printed next this:

i thought compose category path category path of parent category (if any) , category name on current row. subcategories can have subcategories on own, don't know in column category name on current row stored. category name on current row i'm using following formula (found on this site (german)):

{=index(a7:f7;match(true;a7:f7<>"";0))} 

i know there's no category more 5 levels deep, therefore can savely "hard code" e.g. range a7:f7 row 7 , receive "side job" result of above formula.

now comes tricky part (at least me) , reason i'm asking question: need line of parent category can use parent's category path compose category path of subcategory. in case be

{=h4&" > "&index(a7:f7;match(true;a7:f7<>"";0))} 

i don't know how dynamically calculate address of parent's category path. another german site i've got formula goes right direction , return row of parent category "wage & salary":

{=max((a1:a7<>"")*row(a1:a7))} 

but if matrix in formula constant. doesn't work dynamically calculated matrix. dynamic matrix should range a1 a7 in case of "side job" a column left category name, 1 first row (and row want search to) , 7 current row. dynamic part of matrix a , 7.

in order compose matrix (the dynamic_matrix) i'm using formula:

{=indirect(address(1;category_column-1)&":"&address(row();category_column-1))} 

where category_column first formula wrapped in column():

{=column(index(a7:f7;match(true;a7:f7<>"";0)))} 

unfortunately, max(... formula returns error when using dynamic_matrix:

{=max((dynamic_matrix<>"")*row(dynamic_matrix))} 

to bypass error replaced dynamic_matrix<>"" not(isblank(dynamic_matrix)):

{=max(not(isblank(dynamic_matrix))*row(dynamic_matrix))} 

now, that's how formula looks without placeholders:

{=max(not(isblank(indirect(address(1;column(index(a7:f7;match(true;a7:f7<>"";0)))-1)&":"&address(row();column(index(a7:f7;match(true;a7:f7<>"";0)))-1))))*row(indirect(address(1;column(index(a7:f7;match(true;a7:f7<>"";0)))-1)&":"&address(row();column(index(a7:f7;match(true;a7:f7<>"";0)))-1))))} 

my formula returning current row instead of parent category's row (that is, e.g. "side job" returns 7 instead of 4).

i don't know why :(

short, whole formula should following:
if it's subcategory on current row, print parent category's path > subcategory name. parent category's path should income > category name if parent category stored in column a (and therefore has no parent category on own (except "income" group heading)).

it's absolutely sufficient me if wants answer questions focuses on "get parent category's path" part :)

thanks,
marcel

here answer go out 5 levels asked in question.

based on formula here.

=lookup(2,1/($a$2:a2<>""),$a$2:a2) & if(counta($b2:$d2)>0,">" & lookup(2,1/($b$2:b2<>""),$b$2:b2)  & if(counta($c2:$d2)>0,">" & lookup(2,1/($c$2:c2<>""),$c$2:c2)  & if(counta($d2:$d2)>0,">" & lookup(2,1/($d$2:d2<>""),$d$2:d2)  & if(counta($e2:$e2)>0,">" & lookup(2,1/($f$2:f2<>""),$f$2:f2),""),""),""),"") 

notice put income in it's own column, make exceptions when indent in , when don't trouble data management (and data storage format not great).

a better structure in opinion have catagories filled down (more database) , if want can use pivot tables , such analyse data easier.