Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Numbers

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,074
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default


    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  6. #6
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok I did.

    Gotta love being able to edit these messages!

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •