CODEWITHSUNDEEP
excelexcel-formula
marcelbrueckner
marcelbrueckner

Reputation: 367

First used cell above address

I'm creating a budget in Excel and therefore I have some categories and subcategories. Subcategories are intended by 1 cell compared to their respective parent category. Subcategories of course can have subcategories on their own.

In the column next to my "category tree" I want to print the "category path".

Let's say I have the following tree of categories: Category tree

I now want the "category path" to be printed next to it like this: enter image description here

I thought I could compose the category path with the category path of the parent category (if any) and the category name on the current row. As subcategories can have subcategories on their own, I don't know in which column the category name on the current row is stored. To get the category name on the current row I'm using the following formula (found on this site (German)):

{=INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0))}

I know there's no category more than five levels deep, therefore I can savely "hard code" e.g. the range A7:F7 for row 7 and receive "Side job" as result of the above formula.

Now comes the tricky part (at least for me) and the reason I'm asking this question: I need to get the line of the parent category so I can then use the parent's category path to compose the category path of the subcategory. In this case it would be

{=H4&" > "&INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0))}

I don't know how to dynamically calculate the address of the parent's category path. From another German site I've got a formula which goes into the right direction and would return the row of the parent category "Wage & Salary":

{=MAX((A1:A7<>"")*ROW(A1:A7))}

But only if the matrix in the formula is constant. It doesn't work with a dynamically calculated matrix. The dynamic matrix should range from A1 to A7 in case of "Side job" as A is the column left to the category name, 1 is the very first row (and the row I want to search up to) and 7 is the current row. The dynamic part of this matrix is A and 7.

In order to compose the matrix (the DYNAMIC_MATRIX) I'm using this formula:

{=INDIRECT(ADDRESS(1;CATEGORY_COLUMN-1)&":"&ADDRESS(ROW();CATEGORY_COLUMN-1))}

where CATEGORY_COLUMN is my first formula wrapped in COLUMN():

{=COLUMN(INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0)))}

Unfortunately, the MAX(... formula returns an error when using it with the DYNAMIC_MATRIX:

{=MAX((DYNAMIC_MATRIX<>"")*ROW(DYNAMIC_MATRIX))}

To bypass the error I replaced DYNAMIC_MATRIX<>"" with NOT(ISBLANK(DYNAMIC_MATRIX)):

{=MAX(NOT(ISBLANK(DYNAMIC_MATRIX))*ROW(DYNAMIC_MATRIX))}

Now, that's how the formula looks like without the 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 is returning the current row instead of the parent category's row (that is, e.g. for "Side job" it returns 7 instead of 4).

I don't know why :(

Short, the whole formula should do the following:
If it's an subcategory on the current row, print parent category's path > subcategory name. The parent category's path should be Income > category name if the parent category is stored in column A (and therefore has no parent category on their own (except the "Income" group heading)).

It's absolutely sufficient for me if someone who wants to answer my questions only focuses on the "get parent category's path" part :)

Thanks,
Marcel

Upvotes: 3

Views: 202

Answers (1)

gtwebb
gtwebb

Reputation: 3011

Here is an answer that will go out the 5 levels asked for in the question.

Based on a 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 I put income in it's own column, as soon as you make exceptions about when you indent in and when you don't its trouble for data management (and this data storage format is already not great).

enter image description here

A better structure In my opinion is to have All the catagories filled down (more like a database) and then if you want you can use pivot tables and such to analyse the data easier.

Upvotes: 4

Related Questions