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!
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!
