How to auto number Data in an excel sheet?

[Guest]

I want users to enter records into excel and the record to be automatically
numbered, HOW?

 

[Guest]

Hi
in A1 enter
=IF(B1<>"",1,"")

in A2 enter
=IF(B2<>"",MAX($A$1:OFFSET(A2,-1,0))+1,"")
and copy this formula down for as many rows as required. Now everytime
someone enters data in column B column A shows a ID value

 

[Arvi Laanemets]

Hi

A2=IF(B2="";"";ROW()-1)
and copy down

 

[Guest]

Hi Frank,

It gives me two rows of the same number like this:
A1 = 1
A2 = 1
A3 = 2
A4 = 2
..
..
..

 

[Guest]

Hi
just tested the formulas again and they work for me?
What have you entered eXACTLY in A1 and A2

 

[Guest]

Hi,
exactly what you said:
A1 =IF(B1<>"",1,"")
A2 =IF(B2<>"",MAX($A$1:OFFSET(A2,-1,0))+1,"")

and this is how it appears on my sheet
1 MK
1 MK
2 MK
2 XX
3 YY
3 RM
4 XX
4

 

[Guest]

Thanks for your reply, but this automates the number only after you type in
B2. I want the number to be displayed automatically as soon as the previous
record is filled

 

[Arvi Laanemets]

Hi

Simply
A2=IF(B1="","",ROW()-1)
I assume in row 1 are headers - actual data start from row 2. And B1 never
is empty!

 

[Guest]

Great, thanks a bunch

Hi

Simply
A2=IF(B1="","",ROW()-1)
I assume in row 1 are headers - actual data start from row 2. And B1 never
is empty!

 

[Guest]

Hi
and how do you copy cell A2 down?. e.g. what is in cell A3?
Also is automatic calculation enabled or does hitting F9 help?

 

[Aladin Akyurek]

A1 must house a 0.

In A2 enter: Rec#

Row 2 from B2 on must house the headers/fields a record consists of.

In A3 enter & copy down:

=IF(B3<>"",LOOKUP(9.99999999999999E+307,$A$1:A2)+1,"")

B3 houses the first field value of the record the user enters.

If you're on Excel 2003:

After you have the first formula row (row 3), select the data area fro
A2 on including all fields in row 2.

Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

This will eliminate the burden of copying down the formula for eac
record the user enters.