Excel – How to reference a row value in a table in excel 2007 from another row such that sorting table doesn’t change it

microsoft-excel-2007

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?

Best Answer

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)

Related Solutions

Microsoft Excel – Why Does My Document Have 960,000 Empty Rows?

Microsoft has an excellent support document called How to reset the last cell in Excel.

From that document:

The most common cause of the last cell being set outside the worksheet range that is currently in use is excessive formatting. When you format whole rows and columns, some types of formatting can cause the last cell to be set to a cell far below or to the right of the actual range that is in use.

Based on the comments below your question, it certainly seems like the border format that is applied to all rows is the culprit.

I've used the code from the add-in provided from the link above to reduce file sizes from multiple megabytes to a few hundred k.

Excel – How to Dynamically Reference Named Table Column

You nearly had it with INDIRECT(), but your logic was a little off. Try this instead:

=COUNT(INDIRECT("Table1["&A1&"]"))

The key to remember is that INDIRECT() only takes text (i.e., a string) for the first argument. Thus, you have to pass all parts of the table reference to the function as text.

Best Answer

Related Solutions

Microsoft Excel – Why Does My Document Have 960,000 Empty Rows?

Excel – How to Dynamically Reference Named Table Column

Related Question