Thread: sort linked cells in master without losing data in dependent worksheet

I am using Excel to create a project plan for a project that will be going on from now until 2011. I have a master overview of the project that has 123 action items and each action item will have at least 3 or 4 sub-items attached to it. There are 4 key players in this project, and so each of these 400+ tasks is going to be assigned to one of the players.

The worksheet is basically like this:

• Column A: Key Deliverable
• Column B: Action Item
• Column C: Sub-Action Item
• Column D: Completion Date
• Column E through H: One column per key player, each column noting whether the player is responsible to ‘lead’ ‘review’ or ‘participate’ in any of the given tasks. These columns are sometimes empty if a key player has no responsibility for a particular task.
• Column I through AR: One column per month January 2009 through December 2011

In order to make the workplan a little less overwhelming to look at for each of the key players, I want to be able to create a worksheet for each of the 4 key players that lists only their tasks once the master overview is complete. In order to do this, I had thought to link from the master overview as the source to a new worksheet for each of the 4 key players as the dependent files.

However, when I was testing this possibility, I noticed that if I set up the linked data in the dependent files while the master is sorted by ‘Completion Date’ and then resort the master worksheet to being sorted by ‘Key Deliverable’ (as an example) that the links in the dependent files stay with the cell reference, rather than updating with the new location of the data.

Is there a way to set up the links in the dependent files so that when I sort the master file the correct information is retained in the dependent files? Or is there an easier way to approach all of this?

Thank you!

I joined this forum for exactly this same issue. How can I establish a link to a cell on a dependent sheet, and have the link be to the value, not to the fixed cell address. I must sort the master sheet, and that destroys the links, and then that changes the data that shows up in the dependent cells.

It appears that naming or tagging cells might solve the problem, but so far, that does not work either, but tehn, I don't know what I am doing.

I need the dynamic link instructions, please.

Thanks,

BRANDBC