Numbering a list of calculated Rows.......Advice Please

[Guest]

Can anyone advise me how to number the first column of my worksheet.
I want the number to automatically increment when I enter the first cell of
following row. I do not want this number to be entered manually.
I need to be able to delete rows randomly and for the the row numbers to
automatically renumber in numerical order with no numerical gaps.
For example if I have a column 1,2,3,4,5, and delete 3, I would like the
column to automatically renumber as 1234 and not 1,2,4,5.
I hope I have explained myself correctly. I have a copy of Excel 2002
InsideOut, but can't find a method to do this.
Please supply an example if possible.
Regards
Dermot

 

[Bob Phillips]

Use a formula of =ROW()

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the Reply Bob,
Great formula...
Another relevant question...although basic.....
I will try to keep it brief....
I know how to enter the formula in a cell
I could enter the formula in say a group of 20 Cells manually...
I do not know how a shortcut method to enter the formula in all the cells
beyond say row 4 to say infinity.....this would save me the need to return at
a later date to
enter the formula manually every time the rows I have previously entered
enter the formula in run out (Used as a record).
I have tried experimenting with copy and paste but do not get the results I
expected.

Please advise me of the options available to me?
Regards
Dermot

 

[Max]

... a shortcut method to enter the formula in all the cells

One way ..

Type the range you want to fill in the namebox*,
say: A1:A100
*the box with the dropdown just to the left
of the equal sign / formula bar

Enter/Paste into the formula bar, the formula that's supposed to be in the
starting cell in the range, example: =ROW()

Press CTRL+ENTER

A1:A100 will be filled

And if its to be filled to the max A1:A65536, think we could just select col
A, put the formula in the formula bar, then press CTRL+ENTER

 

[tkt_tang]

1. Enters a relatively long worksheet.

2. To divide the worksheet into separate sections by inserting blank
rows as required.

3. The item numbers (as given by =ROW() for each row thereof) would
skip as the case may be.

4. Please advise an alternative.

5. Regards.

 

[Guest]

Thanks again Bob the solution.
After deleting the ROW, I overlooked double clicking on the cell above the
deletd row (at the point where the numerical sequence breaks), Double
clicking on the bottom right corner of the cell when th cursor turns to a
crosshair, restores the numerical order.
I realise this is fundamental, but I am getting here.
Regards to All
That responded.
Dermot

 

[Guest]

Thanks for the information, very useful.
Dermot

One way ..

Type the range you want to fill in the namebox*,
say: A1:A100
*the box with the dropdown just to the left
of the equal sign / formula bar

Enter/Paste into the formula bar, the formula that's supposed to be in the
starting cell in the range, example: =ROW()

Press CTRL+ENTER

A1:A100 will be filled

And if its to be filled to the max A1:A65536, think we could just select col
A, put the formula in the formula bar, then press CTRL+ENTER

 

[Guest]

Hi TKT,
I ventually figured out that Bob's solution above was correct.
Max's posting above explained how to enter the formula in the appropriate
number of cells.
Your posting told me how many cells are in a column....very long!!
When the ROW is deleted, you have to double click on bottom right corner of
the cell above the deleted row, the cells below the deleted row then fall
into numerical sequence. This is fine if the number assighed to the record is
not important, which in my situation is the case.

 

[Max]

You're welcome !

 

[Max]

.. After deleting the ROW, I overlooked double clicking
on the cell above the deletd row
(at the point where the numerical sequence breaks),
Double clicking on the bottom right corner of the cell
when th cursor turns to a crosshair,
restores the numerical order.

As posted in response in your other post in .worksheet.functions:

Found it puzzling why you had to do the above, for row deletions*. The
formula: =ROW() should auto-calc for the rows below which move up. If you
had earlier filled it say to A1:A100, and then you subsequently deleted say,
row50, the re-numbering of all the impacted 49 rows below which move up
should be automatic, w/o requiring another formula fill op. Is the calc
mode switched onto Manual, by any chance ? (Check via: Tools > Options >
Calculation tab)

*For insertions of new rows in between, yes, but not for deletions.

 

[Guest]

Hi Max,
Thanks for the reply.
I wanted to start the numerical sequence from 1 starting at row 6.
I tried using = ROW() but it returned the existing row number of 6.
I wanted to understand the =ROW() function so turned to my book because I
didn't know what it was.The excel inside out book suggested ........
I use =ROW(H1) to start from 1 at row 6. (I don't know what the "H" stands
for?)

Returning to your present posting........
The auto-calc feature is selected in options.
The rows do not automatically renumber for me.
It could be because of the way I have incorporated the formula =ROW(H1) from
row 6........if I have misunderstood your explanation and there is a
corection I could make to have the auto-Calc on deletions then I would
appreciate a further explanation if you can see where I am going wrong?

Quote
*For insertions of new rows in between, yes, but not for deletions.
I appreciate this comment, It would have cropped up later and caught me
unaware If I manged to get the auto - calc to work for this column, I would
have assume i should wok for insertions! Thanks.
Regards
Dermot

 

[Max]

.. to start the numerical sequence from 1 starting at row 6.

One way is to use ROW() & a simple arithmetic correction for the starting
row, example: =ROW()-5. Then fill down down to cover.
Any subsequent row deletions within the filled range would then be catered
for automatically.

I use =ROW(H1) to start from 1 at row 6.
(I don't know what the "H" stands for?)

The "H" is part of a cell reference, H1. "H1" is simply a cell in row 6. We
could also have used: =ROW(A1), =ROW(B1), etc
in the starting cell, and filled down. All would give identical results.
Usually I'd just use: =ROW(A1), and fill down. However, unlike using the
former: =ROW()-5, as you had rightly observed earlier, this method doesn't
auto-adjust for subsequent row deletions in the filled range.

 

[Max]

Oops, typo in line:

.. "H1" is simply a cell in row 6.

should read as:

 

[David McRitchie]

It looks to me like you are piggybacking a different question
onto someone else's thread. Your question reads like a telegram
and is not clear as to the behavior you want and the behavior you
don't want. But since =ROW() would include empty rows, and
you want a different behavior, here is the answer to ...

How to provide an item count that does not include empty rows (based on column A):
A5: (empty or has text value)
A6: =COUNT(A$5:OFFSET(A6,-1,0) +1

a variation would be that would count rows that have text value in Column B
A5: (empty or has text value)
A6: =IF(ISTEXT(B6),COUNT(A$5:OFFSET(A6,-1,0))+1,"")
advantage: the formula could be included even if rest of row is empty and not count.

This is not the best way to work formulas because each formula is
getting it's own count, rather than from the cell above. So if you have a
large worksheet or work additional formulas in the same fashion, you could
have a performance hit. (mentioned in slowresp.htm and proper.htm)
http://groups.google.com/groups?as_umsgid=ugDxrXayCHA.2592@TK2MSFTNGP10

There is no performance hit on =ROW() because it only refers to itself,
but as you know it will not be count of the rows with items of interest to you
when you have insert empty rows.