Outline numbering - non-VBA solution preferred

[ExcelDane]

Hi everybody

I am working on a sheet for scoring KPI's in which I need to have a three-level outline numbering: Themes, sub-themes, and indicators. The format should be "1", "1.1", "1.1.1".

I need to be able to use the data for pivot tables, so each row should include name and number of theme, sub-theme, and indicator.

I have worked out a rather complex (but efficient) solution to perform this task. I would love to attach the file, but it seems this is not possible anymore (?).

I would like a more simple solution if possible, but my main problem is that since my solution refers to cells in the row above, deleting or inserting rows generate an error.

This is easy to fix by copy-pasting the formulas from the top row (I use VBA for this), but for the sake of simplicity, I would prefer a solution which can handle deleting and inserting rows.

I am using Excel 2010 for Windows 7.

Thanks in advance for any ideas!

EDITED: I should add that the reason I am not just copy-pasting the formulas to this post is that I am using the Danish version of Excel which means that for most of you to understand what I am doing I would have to rewrite some very complicated formulas which would probably go wrong at some point.

 

[pgc01]

Hi

1 idea

If in a formula you refer to the row above, for ex. if in A5 you have

=A4+1

if now you delete row 4 then you'll get an error, because the cell it was referring to no longer exists.

If in A5 instead of referring to specifically A4 you refer "the cell in the same column, 1 row above", like:

=INDEX(A:A,ROW()-1)+1

now if you delete row 4 there's no problem. The cell that was previously A5 is now A4 and "the cell in the same column 1 row above" is now A3.

Does this help?

EDITED: I should add that the reason I am not just copy-pasting the formulas to this post is that I am using the Danish version of Excel which means that for most of you to understand what I am doing I would have to rewrite some very complicated formulas which would probably go wrong at some point.

Remark: you don't have to do the formula conversion manually. If you have formulas in Danish you can see them in English using the formula property of the range.

For ex., enter in the immediate window

?range("A5").formula

and you'll get your formula.

 

[ExcelDane]

[SOLVED] Re: Outline numbering - non-VBA solution preferred

Hi pgc01

thanks a lot for your answer. Actually it didn't do the trick itself, but it made me wonder if I could substitute e.g.
C5=C4
with
C5=INDIRECT("C"&ROW(C5)-1)

It worked like a charm.

Such an easy solution for a problem I actually started solving around five years ago

I would like to share my solution, but I am not sure how to understand your trick for doing so. So I did a search and replace in word (didn't think of that in the first place).

Here it is:

The structure of my sheet is as follows (column names first):
C: Theme numbers (top level - X).
D: Theme names. The formula in its present form requires that there is a theme name in each row. I use conditional formatting to show only the first instance of a theme name. The reason I need the theme name in every row is that I need to be able to make pivot tables from the data.
E: Sub-theme numbers (mid-level - X.X).
F: Sub-theme names (must be in each row)
G: Indicator numbers (bottom level - X.X.X)

Starting in row 6, these are the numbering formulas:

Theme numbers:
=IF(ROW($C6)=6;1;IF($D6=INDIRECT("D"&ROW($D6)-1);INDIRECT("C"&ROW($C6)-1);INDIRECT("C"&ROW($C6)-1)+1))

Sub-theme numbers
=IF(ROW($E6)=6;"1.1";IF(AND($F6=INDIRECT("F"&ROW($E6)-1);$C6=INDIRECT("C"&ROW($E6)-1))=TRUE;INDIRECT("E"&ROW($E6)-1);IF($C6=INDIRECT("C"&ROW($E6)-1);INDIRECT("C"&ROW($E6)-1)&"."&RIGHT(INDIRECT("E"&ROW($E6)-1);LEN(INDIRECT("E"&ROW($E6)-1))-FIND(".";INDIRECT("E"&ROW($E6)-1);FIND(".";INDIRECT("E"&ROW($E6)-1))))*1+1;$C6&".1")))

Indicator numbers:
=IF(ROW($G6)=6;"1.1.1";IF(AND($H6=INDIRECT("H"&ROW($G6)-1);$E6=INDIRECT("H"&ROW($E6)-1))=TRUE;INDIRECT("G"&ROW($G6)-1);IF(AND($C6=INDIRECT("C"&ROW($G6)-1);MID(INDIRECT("G"&ROW($G6)-1);FIND(".";INDIRECT("G"&ROW($G6)-1))+1;FIND(".";INDIRECT("G"&ROW($G6)-1);FIND(".";INDIRECT("G"&ROW($G6)-1))+1)-(FIND(".";INDIRECT("G"&ROW($G6)-1))+1))=RIGHT($E6;LEN($E6)-FIND(".";$E6)))=TRUE;LEFT(INDIRECT("G"&ROW($G6)-1);FIND(".";INDIRECT("G"&ROW($G6)-1);FIND(".";INDIRECT("G"&ROW($G6)-1))+1))&RIGHT(INDIRECT("G"&ROW($G6)-1);LEN(INDIRECT("G"&ROW($G6)-1))-FIND(".";INDIRECT("G"&ROW($G6)-1);FIND(".";INDIRECT("G"&ROW($G6)-1))+1))+1;$E6&".1")))

The indicator numbering formula is no doubt the most complex Excel formula I have ever made. I counted 53 functions.
It works, however. And it's a pure non-vba solution which has its advantages.