Problem: I work in human resources, and I have a list of employees, separated by department. I have a numeric sequence in column A and the employees' names in column B. Every time the company hires or fires an employee, I have to manually renumber all the employees. How can I make this job easier?
- Numbering the employees manually is an HR nightmare.
Strategy: You can replace the numbers in column A with a formula that will count the entries in column B. The formula should count from the current row all the way up to row 1.
The COUNT function will not work because it only counts numeric entries. You need to use the COUNTA function and keep in mind the following points:
- The range that should be counted should extend from B1 to the current row.
- The notation to always use B1 is B$1.
Here's what you do:
- Enter the formula =COUNTA(B$1:B2) in cell A2.
- Count from B1 to the current row.
When you copy this formula down a row, the range that is counted will extend from B1 to B3. This is because the B2 portion of the formula is a relative reference that is allowed to change as the formula is copied. The dollar sign in the B$1 reference tells Excel that when you copy the formula, it should always refer to row 1.
- The range now extends from B1 to B3.
The range now extends from B1 to B3.
- Copy the formula down to all the names in your list. They will be numbered just as when you typed in the names in manually.
Results: When an employee leaves the company, you can simply delete the row, and all of the later rows will be renumbered. When you hire a new person, you can insert a blank row, enter the new hire's name, and then copy any formula from another cell in A to the new row.
While this is a specific example, the concept of using a range as an argument where only one portion of the range contains an absolute reference is a common solution to keeping a running total of all cells above the current row.