Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Any idea how to do an Autonumber?

Posted by Jeremy on January 09, 2001 1:14 PM
Is there anyway within excel to do an autonumber like you do in Access?? I'm trying to populate a database, and would like to automatically assign a number to each entry. Any ideas?

Check out our Excel Resources

Re: Any idea how to do an Autonumber?

Posted by Mark w. on January 09, 2001 1:19 PM
Put the value '1' in a column in your data set.
In the cell beneath this value type =, click on
the cell above, type +1, and [Enter]. Copy this
formula down to the last row of your data.


Re: Any idea how to do an Autonumber?

Posted by dd on January 09, 2001 1:26 PM


Or if you want the numbers to adjust automatically when rows are deleted and for blank rows to be un-numbered try this one:
=IF(A1<>"",COUNTA($A$1:A1),"")



Re: Any idea how to do an Autonumber?

Posted by Mark W. on January 09, 2001 2:49 PM
Or even simplier ...

1. Enter '1' in cell A1
2. Enter =OFFSET(A2,-1,)+1 in cell A2, and Copy
Down.


Re: Any idea how to do an Autonumber?

Posted by Mark W. on January 09, 2001 2:56 PM
Or... if you have a column label in cell A1, then
use =SUM(OFFSET(A2,-1,),1) in cell A2 and below.
That'll allow you to even delete the 1st row of
data without any consequences!

Re: Any idea how to do an Autonumber?

Posted by dd on January 09, 2001 3:10 PM


But these last two formulas don't work if a row is inserted.


Re: Any idea how to do an Autonumber?

Posted by Mark W. on January 09, 2001 3:24 PM
I does if you using a Data Form... If you're not
just use Ctrl-' (single quote) to fill in the
new, empty cell.

Re: Any idea how to do an Autonumber?

Posted by dd on January 09, 2001 3:30 PM

But this doesn't update the rest of the numbers, does it?


Re: Any idea how to do an Autonumber?

Posted by Mark W. on January 09, 2001 3:32 PM
Uhhg, the Ctrl-' suggestion wasn't a good one.
Actually, what I like to do, but didn't want to
elaborate before is:

1. With cell A2 active, define a name (e.g, "counter")
with a reference of "=SUM(OFFSET(Sheet1!A2,-1,),1)".
2. Then use "=counter" in cell A2 and below.
3. ...then you can use Ctrl-' to reliably fill-in
a missing value.


Re: Any idea how to do an Autonumber?

Posted by Mark W. on January 09, 2001 3:33 PM
You beat me to the punch. See my comment right
before yours.

Re: Any idea how to do an Autonumber?

Posted by dd on January 09, 2001 3:45 PM


Sorry to be such a nuisance, but what if you want to insert a blank row (and being blank, it should not have a number) ?


Re: Any idea how to do an Autonumber?

Posted by Mark W. on January 09, 2001 4:07 PM
It was my understanding that the original poster
wanted to number all records; therefore, that
field should be filled in at all times as it would
if you were using the Data Form... menu command.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.