Autogenerate Serial Number

Tag: excel Author: yanqiu999 Date: 2009-09-14

I want to Auto generate serial number.

Student Name Serial number Address Group Section Student ID

Monir 07001 Dhaka,Bangladesh Science B SC001B

When I enter a Student name it will automatically generate its Serial number in the place of Serial number .After that if i save it again enter any new student it will generate the next serial number automatically.

How do i can do it. Please help me.

After whcih pattern should the serial number be generated?
It should be 07001 for the first one then again 07002 for the next one.After some entry if i save it and again enter any new student name then it should auto generated in the serial number cell.Let say if i enter two serial number for ten student and save it.When i open that sheet again and try enter any new student name then in the serial number cell it should be automatically generate the next serial number.So that i do not need to memorize the serial number for the next student.Please give me the solution in the excel.
From this and your previous post, I suspect you should be using a database, rather than Excel.
Okay thank you for your concern.

Other Answer1

If I understood your question correctly the excel-solution is rather easy.

Add the serial number for the first student by hand (07001) and fill the remaining serial number fields (the ones below) with the following formula:

Formula: =IF(ISBLANK(A2),"",B1+1) With A2 = cell to the left and B1 = cell above

R1C1 Notation: =IF(ISBLANK(RC[-1]),"",R[-1]C+1)

Edit: To "add" a leading 0 to the number, you need to set the number format of your serial number to the following custom format: 000000


This will not generate "07002", but "7002".
OK, but it's a minor change to make the formula =IF(ISBLANK(RC[-1]),"",Text(R[-1]C+1, "000000"))
Daves formula works and I added another approach to "add" a leading zero.