Numbers

[Allen]

I know their is a simple solution, but I cannot come up with the answer.

Question: You have 200 Students with the numbers 1 through 200 in a column next to the studens name. You delete student #5 and the rows move up.

Problem: My numbers or now 1, 2, 3, 4, 6, etc. How do I make the numbers change when I delete one row which should be 1 through 199.

Thanks in advance

Allen

[Aladin Akyurek]

Lets say that names are in A from A2 on (A1 is empty or holds a label like Nums).

In B2 enter: =IF(LEN(A2),MAX($B$1:B1)+1,"")

Copy down this as far as needed.

[Mark W.]

If your numbers are in column B beginning at B2 enter the formula, =N(OFFSET(B2,-1,))+1, into cell B2 and fill down to last row of your data list. This formula assumes that you have text column labels in row 1.

[ This Message was edited by: Mark W. on 2002-02-21 09:37 ]

[Steve Hartman]

In the first cell where you want the student number,ie B1, put =CELL("row",B1) and copy it down the column of student numbers. If the range doesn't start at cell 1 of the column, ie B10, put =CELL("row",B10)-9. Subtract 1 less than the start cell row.

When you delate a row the formula will still be correct.

[ This Message was edited by: Steve Hartman on 2002-02-21 10:00 ]

[ This Message was edited by: Steve Hartman on 2002-02-21 10:07 ]

[ This Message was edited by: Steve Hartman on 2002-02-21 10:11 ]

[Mark W.]

On 2002-02-21 10:00, Steve Hartman wrote:
In the first cell where you want the student number,ie B1, put =CELL(row,B1) and copy it down the column of student numbers. If the range doesn't start at cell 1 of the column, ie B10, put =CELL(row,B10)-9. Subtract 1 less than the start cell row.

When you delate a row the formula will still be correct.

[ This Message was edited by: Steve Hartman on 2002-02-21 10:00 ]

[ This Message was edited by: Steve Hartman on 2002-02-21 10:07 ]

Make that =CELL("row",B1)

[Steve Hartman]

Ok I did.

Gotta love being able to edit these messages!

[Mark W.]

On 2002-02-21 10:13, Steve Hartman wrote:
Ok I did. [img]/board/images/smiles/icon_smile.gif[/img]

Gotta love being able to edit these messages!

Yeah, not as many "oops" messages...