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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,806
Messages
5,833,778
Members
430,232
Latest member
Testsubject

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top