Cell reference doesn't work with sort

[Factotum]

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!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Managing Director[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob McBob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Director[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Big Mac[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]South Dept[/TD]
[TD][/TD]
[TD]West Dept[/TD]
[TD][/TD]
[TD]East Dept[/TD]
[TD][/TD]
[TD]North Dept[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD][/TD]
[TD]Ted[/TD]
[TD][/TD]
[TD]Suzy[/TD]
[TD][/TD]
[TD]Rufus[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Possibly...

='Sheet1'!$A$1

 

[Factotum]

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.

 

[MARK858]

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.

 

[Factotum]

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!