Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto numbering cells
I have an excel 2000 sheet that has a number of rows that cut across column A
that are in effect group headers. Each of these headings is a whole number, starting with the number 1. e.g. A1 = 1 A5 = 2 A11 = 3 etc. The cells in between the groups headers start with the previous cells whole number and are incremented by .1 i.e. A1 1 A2 1.1 A3 1.2 A4 1.3 A5 2. A6 2.1 A7 2.2 A8 2.3 A9 2.4 A10 2.5 A11 3 A12 3.1 In the example above, if I insert rows into group 1, I have to manually renumber the remaining cells in the group. E.g. if I insert two rows after A1, I have to manually renumber cells A2 to the new A6, 1.1, 1.2 etc. This may not seem like an issue but sometimes I need to create a new 1.1 when I have 50 or so in the group that need to be renumbered. Is there any way that I could select, for example, cells A2 to A6 and have them renumbered automatically 1.1 through 1.5. The range and the starting whole number need to be variable. Odd request I know but any help greatfully received. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto numbering cells
One way:
Can be done in more compact way, but this is easier to follow. Use some helper columns. I start in column B. B1: 1 B5: 2 B11:3 C1:1 C2: =IF(B2=0,C1,B2) D1: 0 D2: =IF(B2=0,D1+1,0) E1: =C1&IF(D1=0,"","."&D1) Copy C2, D2 and E1 down as far as you need. Column C contains your number/subnumber. Hide any columns you don't want to see. If you insert rows, just copy columns C,D and E in the new rows -- Kind Regards, Niek Otten Microsoft MVP - Excel "RichieRich" wrote in message ... I have an excel 2000 sheet that has a number of rows that cut across column A that are in effect group headers. Each of these headings is a whole number, starting with the number 1. e.g. A1 = 1 A5 = 2 A11 = 3 etc. The cells in between the groups headers start with the previous cells whole number and are incremented by .1 i.e. A1 1 A2 1.1 A3 1.2 A4 1.3 A5 2. A6 2.1 A7 2.2 A8 2.3 A9 2.4 A10 2.5 A11 3 A12 3.1 In the example above, if I insert rows into group 1, I have to manually renumber the remaining cells in the group. E.g. if I insert two rows after A1, I have to manually renumber cells A2 to the new A6, 1.1, 1.2 etc. This may not seem like an issue but sometimes I need to create a new 1.1 when I have 50 or so in the group that need to be renumbered. Is there any way that I could select, for example, cells A2 to A6 and have them renumbered automatically 1.1 through 1.5. The range and the starting whole number need to be variable. Odd request I know but any help greatfully received. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Numbering a merged Cells Excel 2003 | Excel Discussion (Misc queries) | |||
Auto-numbering | Excel Worksheet Functions | |||
set up auto numbering of cells | Excel Discussion (Misc queries) | |||
help with auto numbering | Excel Discussion (Misc queries) | |||
How do I set up auto-numbering on cells? | Excel Programming |