Numbers

Allen

New Member
Joined
Feb 20, 2002
Messages
1
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
On 2002-02-21 10:13, Steve Hartman wrote:
Ok I did. /board/images/smiles/icon_smile.gif

Gotta love being able to edit these messages!

Yeah, not as many "oops" messages... :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top