Excel Numbering

[Randy]

Ok.. Not sure if this is possible at all..

I would like to create a column in Excel that automatically numbers. I'm
creating a seniority list for my company.. the most senior employee will be
employee #1.. next senior will be #2.. etc.. the problem is when someone
quits or retires I have to manually renumber everyone underneath that
person.. Is there any way to do this automatically?.. For Administrative
purposes I can arrange employees by hire date.. however employees always ask
what their 'number' is

 

[B. R.Ramachandran]

Hi Randy,

Let's us imagine that the hire dates are in Column B in the range B2 to B101.
Enter one of the following formulas and drag to fill down the last row.

=RANK(B2,$B$2:$B$101,1) Change the range according to your data.

or the following one which is more general (if more employees are added, you
don't have to modify the range in the formula manually)

=RANK(B2,$B:$B,1)

If a row is deleted when an employee leaves or retires, the ranks will
update automatically.

Please give a feedback by clicking "Yes" if this helps.

Happy New Year,
B. R. Ramachandran

 

[Rick Rothstein]

Assuming your "hire dates" are in Column D and that the data starts on Row
2, put this formula in Row 2 of the column that holds the seniority number
and copy it down as far as you like... even past the number of current
employees in order to have the formula in place in case you add new
employees to the worksheet (that way you won't have to edit the formula in
the future)...

=IF(D2="","",RANK(D2,D$2$1000,1))

Use a ending row number in D$2$1000 that is larger than the maximum number
of employees you ever expect to be listed on the worksheet... that way you
won't ever have to edit the formula again.

 

[Bill Kuunders]

You could use =row() in that extra column

If you delete a person or full row the numbers will adjust
If you cannot delete the entire row than you can move the range up after
deleting just the persons name.
You can of course add or subtract........... for instance =row() - 3
if you have three header rows.

Greetings from New Zealand

 

[Rick Rothstein]

I meant to say in my last post... you do not have to sort the data on the
hire date... the RANK function is **not** dependent on the order of the
data... so, you can leave the data sorted in a more normal alphabetical by
last name if you wish.