• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

1.1, 1.2, 1.10, 1.19....

Udi Heller

New Member
Hi

I need to number a development spec on excel and need to numbering the entries.

Using =IF(B2="","",MAX($A$2:A2)+0.1)

i can do 1.1.... 1.9, and than 2.0

but i need 1.1, 1.2... 1.10,... 1.15... and no on until there is a row empty, and than it jumps to the next series (2.0, 4.0... 10.0 and even 45.10 for example)

How can i change the formula to make it so?

10x,
Udi
 
Welcome to Chandoo Org forums!

Your example is bit unclear. Can you expand series clearly and show exactly what you need?
 
Hey

There is a list compiles from developments stages. each feature is numbered. 1.1, 1.2 etc. the code i gave earlier allow me only to work 1.1, 1.2..... 1.9 and than it jumps to 2.0,

what i need for like that

1.1
1.2
....
1.11
1.12

Line break? (empty row)
2.0
etc


10x !
 
Hi Vietm

10x for the help!

however, it dosent do what i need :-(

the first example i dont really understand, it dosent work so will for me
( =IF(B2="",INT(B1)+1.1,B2+0.1) )

for the second, u r using 3 columns, where the third one simply put together the other 2 in the format of X.Y

what i meant is a stand alone column, where the A2 is 1.0 (im writing it)
and starting A3, u have 1.1, 1.2 .... etc, without the limit of 1.9 and than 2.0, but it goes on with 1.11 etc

and where there is an empty row, the formula knows it needs to change the prefix to the next number, 2.0.... etc, again, without the limit of 9.X but it go go on to even 45.X

Any one can be kind and help with the formula for this ? i am sure u can :)

10x a lot
Udi
 
Udi Heller
the 1st: it works as written.

the 2nd: Did You mention somewhere that You can use only one column?
Sheet has many columns ...

Ps. Did You change rules?
eg 1.1 2.1 3.1 could be the possible after empty cell? or something else now?

Please, make crystal clear rules, what would You like to get.
 
Hey again

10x for the nice example of a notebook :)

As for what i need, i really tried to make this work but i cant
=IF(B2="",INT(B1)+1.1,B2+0.1)

it just giving me an error or 1.1 on all rows (A) and i dont know how and what should i change. i am not an excel programmer.

As for what i need, i will to be more specific

1. just one numbered column of the features for development (A1 ...)

2. start value for every new series: X.0

3. each series can go from X.0 to X.99

4. new (following) next series start when there is a black ROW (1.1, 1.11, blank row? 2.0)

5. the series can go up to 99.x

Hope this will help creating the appropriate formula

10x very much !
 
upload_2018-8-27_13-33-56.png

In A2, enter :

=IF(B2="",MAX(A$1:A1)+1,IF(B2<>"",LOOKUP(9^9,A$1:A1)&"."&COUNTIF(A$1:A1,LOOKUP(9^9,A$1:A1)&"*")+1,""))

and, select A2 >> Custom cell format, enter: ;;;@

then, copied down

Regards
Bosco
 

Attachments

  • SerialNumbering.xlsx
    11.7 KB · Views: 25
Hey Bosco,

It doesn't work for me :-(

Both excel 2013 and 2016

I can c your work, it is what i need, but i am unable to replicate it

Or the spreadsheet it dosent do anything, or it shows the formula as it is in the rubric, without the end result or simply the number 1 (and i write 1 on A1, than A2 will show 2 an so does A3 and A4 (all 2)

i do change the format cell to ;;;@

i repeat what ever u did, and it still dosent work.

I dont know why and if i am doing something worng?

10x
 
See if below idea is acceptable.

In cell A2: type 1.1 manually.

In cell A3 put following formula:
=IF(B3<>"",IF(B2<>"",CONCATENATE(INT(A2),".",RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99)+1),INT(LOOKUP(2,1/$A$2:A2,$A$2:A2))+1.1)&"","")

and copy down as much as you need.
 
Hey Bosco,

It doesn't work for me :-(

Both excel 2013 and 2016

I can c your work, it is what i need, but i am unable to replicate it

Or the spreadsheet it dosent do anything, or it shows the formula as it is in the rubric, without the end result or simply the number 1 (and i write 1 on A1, than A2 will show 2 an so does A3 and A4 (all 2)

i do change the format cell to ;;;@

i repeat what ever u did, and it still dosent work.

I dont know why and if i am doing something worng?

10x

Upload your worksheet and let me see how did you used the formula

Regards
Bosco
 
Hi Shrivallabha and Bosco,

your both example, i am sure, i gr8, but they just want apply on my excel :-( i am trying everything in every form i can think of. Shrivallabha just leave the cell blank, and the other one is giving 1 or error

C attachment. 10x !
 

Attachments

  • Example_to_Numbering.xlsx
    20.3 KB · Views: 17
Hi Shrivallabha and Bosco,

your both example, i am sure, i gr8, but they just want apply on my excel :-( i am trying everything in every form i can think of. Shrivallabha just leave the cell blank, and the other one is giving 1 or error

C attachment. 10x !
Numbers will correct if you input something in Feature column as formula currently relies on Column B content. See attached file.
 

Attachments

  • Example_to_Numbering.xlsx
    21.1 KB · Views: 19
i can assure u all that on the original workbook there more more than 1000 rows of data :) so it not that b is empty, but still i am strugeling

well, never mind, i realize it will not help me when i will sort one the collumes since excel will simply keep the same numbering and will not allow 2.1, 1.4, 11.6 one after the other and will always rearrange the record by 1.1, 1.2 etc. so it dosent help me

i will do the numbering manually. how ever i came to another problem...

i need help with custom format of a cell

i need
1.1, 1.2, ...., 1.19,... 2.0... 2.19

however, all the pre defined allow me to do
1.10, 1.20,... 1.11
or 1.1, 1.2... 1.10 becomes 1.1 again :-(

what can i do ?

10x a lot everyone
Udi
 
I took the lazy way out to move the workbook behaviour closer to your defined requirements. Because of the strategy used to build functionality, I was simply able to replace the data input range 'heading.level' by the formula

= IF( ISTEXT(Heading.text), 1, 2 )

where Heading.text is column 7 (or G as it is quaintly known to spreadsheeters that have their own unique way of counting :rolleyes:). This reduces the heading level array to the role of a helper field and I am sure it could be eliminated with minimal effort.
 

Attachments

  • section numbers.xlsx
    17.9 KB · Views: 21
Hi @GraH - Guido

Thanks for the comment. It sometimes surprises me how much human perception is influenced by graphical presentation considerations such as font size and the placement of information. Spreadsheets are often a case of 'Good sums, lousy journalism'; the message simply gets lost.

The attached is the same file but what one now sees now is tabular information held within a series of blocks whereas before is was a text document bounded in size. In reality it is a single Excel Table that grows gracefully as data is added but it lacks the jazzy stripes that herald "I am one of these new-fangled tables things that real spreadsheeters don't use".

Not quite; the level 1 numbering increments when the body text is missing rather than when header text is present and the single-digit heading 1 numbers are no longer output.
 

Attachments

  • section numbers alt.xlsx
    15.7 KB · Views: 21
Back
Top