Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do I create a field that automatically generates a unique number when I enter a new record in a table ?

I have a list of numbers in the B column. I want the numbers in column A to automatically list as 1, 2, 3, 4, .....

BUT

When I insert rows so that I can add additional numbers to the B column, I want the A column to AUTOMATICALLY CORRECT its self and AUTOMATICALLY UPDATE the new empty cells in column A with the sequential numbers 1, 2, 3, 4, 5, 6, ......

Is there some sort of CONDITIONAL FORMATTING that I can use? Formulas do NOT work because the new empty cells in column A dont AUTOMATICALLY FILL themselves.

share|improve this question
    
It would seem that Access is the only way to accomplish what I want. Excel does not have the capability to auto format numbers sequentially after rows are inserted. :-( –  user235748 Jul 4 '13 at 15:22
add comment

3 Answers

What version of Excel? In 2007 and later you can use an Excel table. All formulas and formatting will automatically be applied to new rows.

Use the formula

=Row()

to create a unique number.

enter image description here

Then insert a new row above row 3 and you will see:

enter image description here

If you want the first data row in the table to be a 1, not a 2, use

=row()-1

In Excel 2003 you can use a list. I forget which versions earlier than 2003 support lists, but if you use anything older than 2003 you may want to consider upgrading, anyway. The formula is the same.

Before:

enter image description here

After inserting a new row:

enter image description here

share|improve this answer
add comment

There isn't (adding hooks to Excel, let's face it, is a HUGE security risk and usually isn't worth the trouble). However, you can get reasonably close by filling in more cells than you can reasonably ever use on the spreadsheet. The details for that are available on this SuperUser Question. Just fill the first cell with the formula you mentioned and let the VBA code take care of the rest.

share|improve this answer
add comment

What is this for? If it is for a database, mysql provides a feature for generating a unique number each time a new record is entered. Or you can use Microsoft Access that has a feature like that.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.