I have so many records but I need to enter serial numbers automatically in Excel 2007
. I have hundreds of records. Instead of entering them manually I want them to show up automatically. Please give me short example for 1 to 10.
This question is related to
excel
excel-2007
Simple Answer:
In A1
=if(b1="","",row())
In A2
=if(b2="","",row())
copy this down the column. Each time an entry is entered in column B the next number in order will be entered in column A.
Suppose you want the answer of 2 in a4
cell because of your heading & other stuff,
you can write as
=if(b4="","",row()-2)
Enter the formula =ROW()
into any cell and that cell will show the row number as its value.
If you want 1001, 1002 etc just enter =1000+ROW()
I find it easier using this formula
=IF(B2<>"",TEXT(ROW(A1),"IR-0000"),"")
Need to paste this formula at A2, that means when you are encoding data at B cell the A cell will automatically input the serial code and when there's no data the cell will stay blank....you can change the "IR" to any first letter code you want to be placed in your row.
Hope it helps
Use formula =row(b2)-x, where x will adjust the entries so that the first S/No is marked as 1 and will increment with the rows.
=IF(B1<>"",COUNTA($B$1:B1)&".","")
Using formula
If you want to pick cell entries from a list then you have a couple of non-code based options
I would recommend The Data Validation approach where
sample from Debra's site below, click on the first link above to access it.
Source: Stackoverflow.com