R
Rannoia
I want users to enter records into excel and the record to be automatically
numbered, HOW?
numbered, HOW?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Frank Kabel
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
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
A
Arvi Laanemets
Hi
A2=IF(B2="";"";ROW()-1)
and copy down
A2=IF(B2="";"";ROW()-1)
and copy down
R
rannoia
Hi Frank,
It gives me two rows of the same number like this:
A1 = 1
A2 = 1
A3 = 2
A4 = 2
..
..
..
It gives me two rows of the same number like this:
A1 = 1
A2 = 1
A3 = 2
A4 = 2
..
..
..
F
Frank Kabel
Hi
just tested the formulas again and they work for me?
What have you entered eXACTLY in A1 and A2
just tested the formulas again and they work for me?
What have you entered eXACTLY in A1 and A2
R
rannoia
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
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
R
rannoia
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
B2. I want the number to be displayed automatically as soon as the previous
record is filled
A
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!
Simply
A2=IF(B1="","",ROW()-1)
I assume in row 1 are headers - actual data start from row 2. And B1 never
is empty!
R
rannoia
Great, thanks a bunch
Arvi Laanemets said: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!
F
Frank Kabel
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?
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?
A
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.
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.