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: category tree

i want "category path" printed next this: enter image description here

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).

enter image description here

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


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more