Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Initial table

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.

Table after sort

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?

share|improve this question
add comment

5 Answers

up vote 2 down vote accepted

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)

share|improve this answer
    
Nice. Simple solution :) Thanks! Though I wish if there can be a solution which avoids this duplication, but I guess with excel, most solutions to tricky problems involves some form of duplication. –  Ankush Jan 13 '12 at 13:46
add comment

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.

share|improve this answer
    
I've one concern. As you mentioned table_array for vlookup needs to be sorted. Lets say initially I've sorted array with manager column referenced like =A3 before, if I add new employee detail into this table I will face the same old problem again when I sort. How to prevent this? –  Ankush Jan 13 '12 at 5:32
    
I found this... support.microsoft.com/kb/181212 this should work? –  Ankush Jan 13 '12 at 5:35
add comment

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: Unsorted VLOOKUP 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.

share|improve this answer
    
No Id. But in my case employee name is unique. Can be treated as Id. Won't I've to keep this id sorted. I came to know that VLookup needs sorted array for lookup. –  Ankush Jan 13 '12 at 13:49
    
Sorry, I forgot one detail. Set the fourth parameter to false, to tell the function that the data is un-sorted. I'll update my answer. –  daxlerod Jan 13 '12 at 14:13
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
1  
I tried that. It doesn't work. It replaces the value to whatever fills up after sorting in A3. I even tried $A3 but that didn't do the trick either. –  Ankush Jan 12 '12 at 19:57
    
You might try using clear all in those cells and re-entering the references. –  CharlieRB Jan 12 '12 at 20:01
    
this is a huge list. Sorting is a frequent operation. in above case I mentioned age only, but in actual case, I'll have more such columns and more sort operations. Re-entering references is not an option. –  Ankush Jan 12 '12 at 20:05
    
You may want to add the detail of the large size to your original question. Sorting cells by the result of a formula can cause issues, but this seems simple enough it should work. Only other thing I can think of is to make sure the entire column is formated as text. –  CharlieRB Jan 12 '12 at 21:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.