Materialized path (aka legal numbering)

[strawberry]

Currently I automate row numbering in my spreadsheet using a formula
like this:

=SUM(OFFSET(A2,-1,0)+0.01)

Where 'A2' is the current cell.

I'd like to extend the idea to construct something more akin to a
'materialized path' or 'legal numbering'. Does anyone have experience
of doing this with Excel?

 

[strawberry]

Hi Zac

One way
Use column A for the major numbering, entering 1,2 3 etc in whichever rows
you wish
Then in B2 enter
=IF(A2<>"",0,LOOKUP(99^99,$A$1:A2)+IF(B1<>0,(ROW()-1)/100,
(ROW()-MATCH(LOOKUP(99^99,$A$1:A2),$A:$A))/100))

and copy down
Format Column B as Number, with 2 places of decimal.

You can use Conditional Formatting to hide the 0.00 which appears in column
B when there is a change of major number in column A

--

Regards
Roger Govier










__________ Information from ESET Smart Security, version of virus signature database 5430 (20100907) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hi Roger,

Thanks for that - but I'm struggling to implement it successfully. Is
there a step missing or am I just being very silly?

 

[Roger Govier]

Hi Zac


Apologies, I sent an earlier formula that I had been experimenting with, not
the final formula which should have been

=IF(A1<>"",0,LOOKUP(99^99,$A$1:A1)+
(ROW()-MATCH(LOOKUP(99^99,$A$1:A1),$A:$A))/100)

I will mail you direct with the workbook I set up.
Perhaps I am not understanding what you are trying to do.

(Incidentally, I am not seeing any of my postings showing up in the
newsgroups)
--

Regards
Roger Govier

Hi Roger,

Thanks for that - but I'm struggling to implement it successfully. Is
there a step missing or am I just being very silly?

__________ Information from ESET Smart Security, version of virus
signature database 5430 (20100907) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5430 (20100907) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[strawberry]

Hi Roger,

Thanks for that - but I'm struggling to implement it successfully. Is
there a step missing or am I just being very silly?

Oh, it works now (not sure what went wrong before)