Cell reference doesn't work with sort


Board Regular
May 14, 2015
Can someone tell me if there is a simple way to retain the original results of a formula when the source data is sorted? I have two sheets: an Organization List and an Organization Chart. The Chart is a visual representation of the hierarchy (who reports to who). If you can imagine some arrows in the table below, my chart shows how each department is related and who they report to (all Departments reporting up to Director and then to Managing Director).

The List, on the other hand, is a very functional list with lots more data than just the department and employee names. It can be sorted, filtered, subtotaled, etc. I want to link the two so that the List can be updated as employees come and go, and those changes automatically populate in the Chart. If Suzy quits and is replaced by Jim, I want to replace Suzy with Jim in the List and have Jim still show up in the Chart under the East Dept where Suzy was.

In other words, I want to link the cell that says Bob McBob on the Chart with Cell A1 in the other sheet like so: ='Sheet1'!A1
The problem is that when I sort the List Z to A, for example, the Managing Director is now Ted on the Chart.

Sorry for the clunky explanation. Thanks for any ideas!

Managing Director
Bob McBob
Big Mac
South DeptWest DeptEast DeptNorth Dept



Board Regular
May 14, 2015
Thanks for the reply. No, unfortunately the absolute reference doesn't make any difference. When I sort the source data, the Chart shows the new value that is now in the cell on Sheet1. It still references cell A1 but the value in that cell has changed and so the value in the Chart has also changed.


MrExcel MVP
Nov 12, 2010
Office Version
365, 2010
Windows, Mobile
Teach me for reading a question too quick, as far as I know a chart will always reference a cell you tell it to so if A1 changes so will the chart.

Perhaps if they come on line someone like Mr Peltier will know a way and enlighten us.


Board Regular
May 14, 2015
Anyone else have any suggestions before I scrap the project and redesign it? Essentially, all I'm trying to do is build a functional list of employees that can be sorted, filtered, summed, etc. and a visual chart of the hierarchy that is linked to the list. The chart structure will almost never change, but the individuals in the list will change frequently, which is why I want it linked to the chart. Thanks!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...