How do I auto-number rows in excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create an invoice log where I need to number the cells. I have
been told that excel is capable of numbering the log itself. I would be very
grateful if somebody could inform me how.
 
Hi

You can use the ROW() function to put a number against each line (row 1 will
get 1, row 2 will get 2 etc) .. and if you move lines around or insert lines
each line will automatically renumber. However, if you do insert lines, the
new line will be blank so you will have to either copy the line above or put
=ROW() in the cell that you want to use to number the rows.

Is this what you're after, or do your invoices have specific numbers and if
you insert rows do you really want you invoice numbers to change. If you'ld
like to provide more details maybe we can come up with a better solution.

Cheers
JulieD
 
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
 
Hi Julie
one main benefit: It also deals with some blank rows in between and is
also robust regarding deleting rows in between. So if you always fill
your list from the top and have no blanks in between no benefit but the
drawback of a volatile function (OFFSET)
 
You should avoid using cell references in your solution as copy/paste and delete operations may retain bad references. The below formula doesn't care where it is and where it is moved. It will always work.

=IF(OR(ROW()=1,ISTEXT(INDIRECT(ADDRESS(ROW()-1,COLUMN())))),1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)
 
Back
Top