# Sorting with formulas that are linked between rows

#### djr1904

##### New Member
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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.

Replies
3
Views
232
Replies
1
Views
135
Replies
5
Views
261
Replies
2
Views
434
Replies
13
Views
504

1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

### 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.

### Which adblocker are you using?

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

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