How to reference a row value in a table in excel 2007 from another row such that sorting table doesn't change it?

Question

Consider a table of employee name, age and employee manager. Employee manager is a reference to employee name in same table but different row. I tried this by referencing cell =A3

Now if I sort this table by Age, the employee manager column gets messed up and doesn't point to original intended manager. Instead it points to what's relative to old row.

So how to reference a row value (manager) in a table in excel 2007 from another row (employee) such that sorting(by age) table doesn't change it?

Answer 1

The simplest way is to create a copy of the Employee Name column outside the table range. It can be sorted or not, doesn't matter. It can be on the same sheet, a different sheet, or even a different workbook, just not part of the table.

Then in the Manager column put formulas refering to this new range using $E$1 format (assuming the copy is in column E)

Answer 2

You need another table somewhere else (like another worksheet in the same file) that contains the Employee-Manager relationship. There you can reference the manager name from the existing list of employees, as you are currently doing in the original spreadsheet.

Once you have this done, you can use the VLOOKUP function to look up an employee's manager by the employee name. Given your example above, for contents for cell C2 would be =Vlookup(A2,Sheet2!$A$2:$B$6,2).

This is the syntax for VLOOKUP: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). So in the example I just gave, lookup_value = employee cell, table_array = the new employee-manager table, and col_index_num = 2, since you want to return the 2nd column of the employee-manager table (the manager's name). This assumes you have placed the new employee-manager table on Sheet2 and started listing names in cell A2.

Even though your names list is "huge", you should be able to copy/paste it into the new sheet to save you typing time.

I hope I have explained this clearly enough. Additionally, for VLOOKUP to work right, the table_array has to be sorted in alphabetical order. Using Help on VLOOKUP will give you more information.

Answer 3

Do you have an employee ID column? If not, add a serial number to each employee, then you can do a VLOOKUP based on the employee ID of the manager to get the name of the manager.

Microsoft Documentation on VLOOKUP

Take a look at my example:

Column A is the Employee ID, Column B is the Employee Name, Column C is the Employee ID of the manager, Column D is the Manager's Name.

The formula I'm using in D1 is =VLOOKUP(C1,$A$1:$B$5,2,FALSE)

By setting the fourth parameter to false, I've told VLOOKUP that the data is not sorted.

By default, or when the parameter is explicitly true, VLOOKUP assumes the data is sorted, and finds an approximate value. Setting it to false forces VLOOKUP to find an exact match.

Note that row 1 has no value in column C. Allan has no manager, and D1 shows #N/A.

Answer 4

I would advise against sorting tables that include formulas, especially with references to other cells within the same table. Unless your data is dynamic (i.e., it absolutely must retain all formulas), I would copy the table and paste the values only (using Paste Special) back onto the table.

If you must retain the formulas but need to do several ad hoc sorts, I would suggest creating a second values-only working copy of the table to sort on a separate sheet.

Answer 5

Use an absolute reference in those cells and it should sort for you. For example; =$A$3