Cell reference doesn't work with sort


Board Regular
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
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.


Well-known Member
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
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!

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...