First used cell above address

Question

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:

I now want the "category path" to be printed next to it like this:

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

Best. Question. Ever. Wish I could upvote you 100 times. Are you only looking to do this with formulas? — findwindow, Apr 21 '16 at 22:25
Thank you for the compliment :) Actually I want to code a javascript application for my finances. Since I have to read up to the current frameworks first I want to use Excel as a temporary solution. With the category path I want to be able to sum the different transactions of a category I add on a separate sheet. — marcelbrueckner, Apr 21 '16 at 22:28
Ok. I am bad at formulas but I may be able to do this via VBA. So, do you just want formulas or is VBA ok? — findwindow, Apr 21 '16 at 22:31
I'm looking for a formula-based approach. I would like to use VBA only if it's not possible with formulas. — marcelbrueckner, Apr 21 '16 at 22:32
Ping Scott Craner tomorrow. He will do this in 2 seconds ^_^ — findwindow, Apr 21 '16 at 22:33
Wait. Hmm. You just want sums? You can hack it by adding an "ID" to each line then use `sumif`? Edit: by "ID" I mean number each line/catergory. — findwindow, Apr 21 '16 at 22:34
Yes, I would use `SUMIF` with the category path but I also want to use the list of category paths for a dropdown menu when entering my transactions. — marcelbrueckner, Apr 21 '16 at 22:37
Oh ok so this is pretty involved and you know what you're doing so yea, ask Scott tomorrow. — findwindow, Apr 21 '16 at 22:40
The only thing that would have made this better is if I did not need to type the data to test the formula. Next time pleas along with the photos post the data as text so we may copy and paste. Other than that good job explaining your thought process. — Scott Craner, Apr 22 '16 at 00:06
As @gtwebb mentions in his answer, could you not just fill down the blank cells? That would make it much easier to do. — BruceWayne, Apr 22 '16 at 05:20
@ScottCraner Thank you for your compliment and sorry for the inconvenience. Next time I will follow your advice :) — marcelbrueckner, Apr 22 '16 at 10:20
@BruceWayne Yes, it would be easier. But it wouldn't look so nice anymore :) — marcelbrueckner, Apr 22 '16 at 10:20
If it's also the way it looks, what I often do with copied down info, is to a Conditional Formatting of say `B2=B1` and when that's TRUE, format the text white, that way it "disappears". — BruceWayne, Apr 22 '16 at 13:57

score 4 · Accepted Answer · edited May 23 '17 at 12:07

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

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.

Thank you very much. This did the trick for me. Additionally, it's much easier than my approach and is therefore easier to understand and to extend at a later time if necessary. Good job :) — marcelbrueckner, Apr 22 '16 at 10:22

First used cell above address

1 Answers1

Linked