Can I add and update a reference number

P

peadar

I would like to add column at the beginning (left most) of a sheet whic
would serve as a reference number, i.e. 1, 2, 3, ..., n, to identify
row.
I don't want to use the Row and column headings check box under pag
setup because that numbers from the very first row, and there are
rows used for titles on every page.
I would like this to automatically get updated if I insert a row an
add data or if I add data after the current last row.
Is there an option I can use or a function I can create?
Thank you, Pete
 
R

RagDyeR

It depends on exactly what you mean when you say "automatically".

I would consider this as semi-automatic.

If you insert a row, all you have to do is click in the row above the insert
and drag down to copy to the row below the insert, to renumber the *entire*
rest of the column.

For deletions, just copy down a row.

For example, enter one (1) in A1, and this in A2:

=A1+1

And copy down as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I would like to add column at the beginning (left most) of a sheet which
would serve as a reference number, i.e. 1, 2, 3, ..., n, to identify a
row.
I don't want to use the Row and column headings check box under page
setup because that numbers from the very first row, and there are 2
rows used for titles on every page.
I would like this to automatically get updated if I insert a row and
add data or if I add data after the current last row.
Is there an option I can use or a function I can create?
Thank you, Peter
 
A

AlfD

Hi!

Or use =row()-2.

But you still have to copy down the formula if you insert a row.

VBA would provide a route but is that what you want?

Al
 
T

TKT-Tang

1. Enter 1 in A3 ; Enter COUNT(A$3:A3)+1 in A4 and copy down to nth
row.
2. Should there be blank rows amidst the range to nth row, minus
COUNTBLANK(A$3:A3)from COUNT accordingly.
3. Regards.
 
P

peadar

VBA would provide a route but is that what you want?

I don't know.
Based on what I have read in other messages here,
I would say I am an average Excel user.
But I could not even say I am a VBA beginner or novice.
(It seems, based on your question, that there is some down side t
VBA).
If there was some bit of code you posted and it was easy enough for m
to insert, then, yes, I'll go that route.
Otherwise, I guess not.
Thank you, Pete
 
A

AlfD

Hi Peter!

There must be an infinite number of ways of doing it in VBA: not all o
them good! I use a bit of VBA but I'm not an expert. However, her
goes.

One simple way would be to set up a capability to renumber the row
after you have inserted new ones or deleted old ones.

Private Sub CommandButton1_Click()
Range("A3").Select
Range("A3").Value = 1
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step :=1
Stop:=5000
End Sub

This just starts with a 1 in A3 and uses the usual Edit>Fill>Serie
command to fill 5000 cells. Change 5000 to what suits you.

It is pasted into the code page behind the worksheet on which your dat
sits. To do this: go to the worksheet in question:Alt+F11 then take
you to the code page.

The way it is written at the moment requires a command button on you
worksheet (from the Control Toolbox found under View>Toolbars). It
natural name will be CommandButton1 (as in the sub) but such things ca
all be changed when needed/wanted.

When the button is clicked, the cells are filled.

This is not elegant stuff, but it works.
And it can be achieved largely by recording a macro.

Try it in a blank workbook. Then you might want to explore other way
of triggering the routine (keyboard shortcuts; toolbar buttons...). Th
ultimate would be to have it triggered by inserting a row, I suppose
Must look at that: never tried it.

Al
 
Top