Automatic Numbering Rows

[A West]

Hi all...
Can't find how do to this using Excel 2003...
I want to be able to have Excel automatically number rows (in the
first column) that have text on them...but skip numbering rows
without...and adjust the numbering if new rows get added in later. (I
guess something similar to Bullets and Numbering in Word).
Here's an example of what I want if I'm not making myself clear...

1. Text Here
2. Text Here
3. Text Here
(Empty row...no text)
4. Text Here
5. Text Here
(Empty row...no text)
6. Text Here
etc..

Then...let's say...at a later date...I inserted a new row under #2.
Hopefully, I can get excel to adjust each rows number below so I don't
have to do it manually...
Is this possible in Excel?
Any help would be appreciated.
Thanks..

aw

 

[Frank Kabel]

Hi
one way:
if your data starts in row two enter the following formula in A2:
=IF(B2<>"",MAX($A$1:$A1)+1,"")
and copy this down

If your data starts in row 1 enter the following in cell A1:
=IF(B1<>"",1,"")
and in A2 the formula from above

 

[David McRitchie]

very nice, I would modify the formula to allow for easier
insertion / deletion of rows by using offset, makes maintenance
a lot easier.

for cell A2: -- then fill down
=IF(B2<>"",MAX($A$1:OFFSET($A2,-1,0))+1,"")

 

[Frank Kabel]

Hi David
that's a good addition (should cover all deletions besides deleting row
1)

 

[A West]

Hi guys...
Thanks for the help..but they don't seem to work. I've tried each of
the formulas suggested...in each cell suggested...but Excel is not
numbering anything automatically as I hoped.
Am I missing something here? (I'm a bit of a novice with Excel)...
is there something else I need to do...type...configure for this to
happen?

Thanks again for the effort...


aw

 

[Andy B]

Hi

Just for your interest, Frank's formula works fine for me (Office 2K). I
simply copied it from the post, pasted it into A2 and filled it down. Now,
if I type anything in B2, B7, B23, or whatever, column A puts the correct
number alongside. I haven't got David's formula to work, though. It shows
the correct numbers when I first use it, but when I add an extra entry into
a B cell it renumbers is as the same as the next line.

Andy.

 

[JE McGimpsey]

Have you copied the formulae in column A down past where you have data
in column B? For instance, if you use David McRitchie's

A2: =IF(B2<>"",MAX($A$1:OFFSET($A2,-1,0))+1,"")

and you have 100 rows of data, have you copied A2 down to, say A1000? Or
for as many rows as you're likely to have data?

 

[blaster452]

FYI - I tried both of those formulas and they both worked
great for me!

 

[David McRitchie]

Since you are the original poster and you did not indicate
success perhaps your concept of automatically differs.
You have to use the fill handle to copy the formula down.
http://www.mvps.org/dmcritchie/excel/fillhand.htm

If you want something truly automatic like filling in the
cell with the formula when an entry is made on another
cell in the row you would need an Event macro but let's
get through the concept of the fill handle first if that is
the stumbling block.

 

[A West]

Success!!!
After checking out your web page...I understand the fillhand concept. Wasn't
doing that at all...I tried a number of different things (copy/paste the
formula...inserting in each cell..etc.) none of which seemed to work. After
reading your fillhand web page...I tried it again...and it WORKED!! It does
exactly what I had hoped...no need for the Macro (thank goodness!).

Just want to say thanks David (and Frank) for the help. You just made a whole
lot of work a whole lot easier!

Cheers!


aw

Since you are the original poster and you did not indicate
success perhaps your concept of automatically differs.
You have to use the fill handle to copy the formula down.
http://www.mvps.org/dmcritchie/excel/fillhand.htm

If you want something truly automatic like filling in the
cell with the formula when an entry is made on another
cell in the row you would need an Event macro but let's
get through the concept of the fill handle first if that is
the stumbling block.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi guys...
Thanks for the help..but they don't seem to work. I've tried each of
the formulas suggested...in each cell suggested...but Excel is not
numbering anything automatically as I hoped.
Am I missing something here? (I'm a bit of a novice with Excel)...
is there something else I need to do...type...configure for this to
happen?

Thanks again for the effort...


aw

 

[David McRitchie]

Great. I added a small picture of showing the fill handle should be even
easier for the next person.

 

[colorguy]

modifying this formula

This is a great thread but I can't figure out how to modify the formula to make it work for my application. I am using column A for my numbering, and I also want the numbers to auto-increment when there is data in column B but to skip blank rows. Here is the issue- on my sheet, row 1 is for labels. Row 2 is blank, and I want to put the formula in A2 and use the fillhand function to copy it down. BUT- starting with row 3 (cell A3) many of these cells are already numbered. The first number is 1683, and it goes to 1769 at row 101.

Should I start at A2 and have it copy over those numbers, or should I start at row 103 (102 is blank) and have it begin with 1770? How should the formula read for this?

Thanks!!