Auto Num. ID

[ZacCerrato]

I would like to create a unique, numeric ID for each new line of a spreadsheet, similar to Access databases.

Is there a way for Excel to automatically create a numeric ID when information is entered onto a new row (i.e. B1, B2, B3), but to leave blank rows without a number? (Again, similar to the way that Access databases create unique numbers for rows.)

 

[ZacCerrato]

*bump*

 

[Joe4]

First, I would recommend waiting a bit longer than 27 minutes before bumping your posts. Note that when you bump them, they no longer appears on the list of threads with no replies, which lessens the views that it might get. I would recommend waiting at least a few hours before bumping. Many of the veterans here search for posts made within the past 24 hours with no replies first.

Back to your question, you get use a formula like this (assuming entering on row 1):

=IF(LEN(B1)>0,ROW(),"")

What this will do is if there is a non-blank entry in column B (on row 1), it will return the current row number, else it will return nothing. So if you copy that down for all rows, it will propulate the row numbers as entries are made in column B.

Alternatively, you may also be able to use a VBA solution that adds a counter as entries are made into column B (using a Worksheet_Change event procedure).