Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Numbering
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 -- Randy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Numbering
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 "Randy" wrote: 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 -- Randy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Numbering
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:D$1000,1)) Use a ending row number in D$2:D$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. -- Rick (MVP - Excel) "Randy" wrote in message ... 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 -- Randy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Numbering
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 "Randy" wrote in message ... 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 -- Randy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Numbering
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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:D$1000,1)) Use a ending row number in D$2:D$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. -- Rick (MVP - Excel) "Randy" wrote in message ... 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 -- Randy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numbering in excel | Excel Discussion (Misc queries) | |||
PO with sequential numbering with start / end numbering | Excel Discussion (Misc queries) | |||
Excel 2000 numbering add-in | Excel Discussion (Misc queries) | |||
Automatic numbering in excel | New Users to Excel | |||
excel numbering add-in | Excel Discussion (Misc queries) |