Sorting with formulas that are linked between rows

djr1904

New Member
Joined
Dec 2, 2005
Messages
1
I'm using Excel 2003.

I created a rather uncomplicated spreadsheet to help me manage my projects. What I do isn't complicated enough to require project management software, so I just set up a few columns that allow me to enter a brief description of each project, any comments, my deadline date for the project, and my completed date for the project. All dates are formatted MM/DD/YY.

My issue relates to the sorting of this spreadsheet. I sort first by completed date, so that completed projects are at the top. Next, I sort by deadline date, so that non-completed projects appear in the order they need to be completed.

Many of the projects on the sheet are connected in some way. Here's an example: Project #1 is to research a topic, followed by Project #2 which is to write a one-page summary of that topic. And Project #2 needs to be completed a week after Project #1. (And there may be 10 additional related projects that need to follow at periodic intervals after these projects are done.)

What I would like to be able to do is, rather than enter a MM/DD/YY deadline date for Project #2, to be able to enter a formula like this: =[cell reference to Project #1 deadline date] + 7

This way if for some reason I need to change the deadline date on Project #1, then the deadline date on Project #2 is automatically adjusted for a deadline date of 7 days later.

This works fine, until I try to sort. Whether I use an absolute or relative cell reference, the formula continues to reference whatever cell I've told it to reference, even if that cell has moved because of the sort. So if my formula is =D47+7, the formula always references D47 even after I've sorted.

Ultimately, I want to be able to 1) connect deadline dates of my projects, and 2) have those connected deadline dates hold up after I sort. So whether the solution is in a different formula or a different way to sort, I'm open to suggestions.

Any ideas on how I could make this work without overly complicating the spreadsheet? Thanks in advance!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
You could use a vlookup to reference your linked project.

If the Project Name is in Column C
Completion date is in Column D
Start Date for next Project is in Column E

=VLOOKUP("Project 10",$C$8:$D$20,2,0)+7

Project 10 being the Project you wish to link the start date too.
Change the range to fit your needs.

This way no matter how the data is sorted the start date will always look for Project 10's end date.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,386
Messages
5,571,829
Members
412,421
Latest member
grace_abar
Top