XL 2003 - Formula Issue: Numbering Disruption If Row Is Moved

[TechTank]

Hi,

I have rows that have the following formula in them to give them a "Serial" number:

Example of one cells code:

=B3+0.01

Every time I add a new row (with a macro that copies a formatted row from another sheet) it adds a 0.01 to the "Serial" thus incrementing the number e.g.

Cell A1 = 0.01
Cell A2 = 0.02
Cell A3 = 0.03 etc.

This works fantastically until I cut and paste a row breaking the "Serial" order. e.g. Moving Row 3 in-between Row 1 & 2:

Cell A1 = 0.01
Cell A2 = 0.03
Cell A3 = 0.02 etc.

Does anyone know of a way to have the formula "auto update" the number to be an increment of the row above so that if I moved the rows it will remain in numerical order?

Thank you for reading and any help you can give me. Think I'm having an "idiot" day and just can't seem to find the answer in my head or on the net.

Mark.

 

[Peter_SSs]

Does this work for you?

Put this formula in A1 and copy down
=ROW()/100

 

[TechTank]

Sorry Peter, but the way I have my sheet set is possibly a little different.

The rows are inserted with a macro from another sheet that has the formatting and data validation set. These rows are added under headers and the sheets are locked so Users can only edit cells in the rows they insert. To save some time and typing I thought it a good idea to put the above formula into the "Serial" box i.e.

A B
<TABLE style="WIDTH: 376pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=501 x:str><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1706" width=40><COL style="WIDTH: 346pt; mso-width-source: userset; mso-width-alt: 19669" width=461><TBODY><TR style="HEIGHT: 50.25pt; mso-height-source: userset" height=67><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 30pt; HEIGHT: 50.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=67 width=40>Serial</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 346pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=461>Header 1</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 376pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=501 x:str><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1706" width=40><COL style="WIDTH: 346pt; mso-width-source: userset; mso-width-alt: 19669" width=461><TBODY><TR style="HEIGHT: 27.75pt; mso-height-source: userset" height=37><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 27.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=37 vAlign=top width=40 align=left><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 30pt; HEIGHT: 27.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl27 height=37 width=40 x:num></TD></TR></TBODY></TABLE></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 346pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl25 width=461>Header Row</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=22 x:num="1.11" x:fmla="=A1+0.01">1.1.1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ff9900; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl24 width=461></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=22 x:num="1.12" x:fmla="=A2+0.01">1.1.2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ff9900; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl24 width=461></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=22 x:num="1.13" x:fmla="=A3+0.01">1.1.3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ff9900; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl24 width=461></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=22 x:num="1.14" x:fmla="=A4+0.01">1.1.4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ff9900; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl24 width=461></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=22 x:num="1.15" x:fmla="=A5+0.01">1.1.5</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ff9900; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl24 width=461></TD></TR></TBODY></TABLE>

As you can see the serials are fine until I move say the row "1.1.4". It will retain that number as the formula "has fixed it" when it was copied in from the other sheet. The Cell is A5 while the formula is still looking at Cell A7 and not A4 as it should be. The output I get is:

<TABLE style="WIDTH: 30pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=40 x:str><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1706" width=40><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 16.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=22 width=40 x:num="1.11">1.1.1</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=22 x:num="1.14" x:fmla="=A4+0.01">1.1.4</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=22 x:num="1.12" x:fmla="=A1+0.01">1.1.2</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=22 x:num="1.13" x:fmla="=A3+0.01">1.1.3</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=22 x:num="1.15" x:fmla="=A2+0.01">1.1.5</TD></TR></TBODY></TABLE>

The only rows that are visible to a User are the rows they insert using the macro assigned to a button to be used "on the fly".

Hope this explains a little better what I'm trying to accomplish.

Kind regards.

Mark.

 

[Peter_SSs]

I don't think I understand enough of this to know whether I can help or not. Can you provide specifics of sheet names, what is in the sheets, macro code, what you are trying to achieve etc.

If posting small screen shots, it would be better if we could clearly see the rows, columns, formulas etc. The first two suggested screen shot methods in my signature block can show all those things.