Forum Discussion
Excel for simple project management - date dependency problems
Hello friends - I have set up a spreadsheet with dozens of tasks related to producing a children's theatre production. Each task's target due date is dependent on either the date we begin rehearsing, or the date the show opens, and is always a set # of days or weeks from that key date. For example, we always strive to choose the show six months out from the first rehearsal. I type the date of the first rehearsal into a cell - let's say D1. Then on the "choose the show" task line, I type "D1-180" into the due date cell, and it calculates and shows a date 180 days before the date in D1. So far so good.
I do this for maybe 300 tasks - always referring back to either D1 (date of first rehearsal) or D2 (opening of show) with the same kind of formula (D2+10 for ten days after opening, etc.). The trouble comes when I want to apply a filter to the due date column and attempt to sort by ascending due date so I can see what needs to be seen to each week. When I do the sort, the cells calculating the individual due dates lose their relationship with D1 or D2 and I get the dreaded REF!.
How do I set
1 Reply
There are at least several ways to address this, and much depends on things you haven't told us.
- How, for example, are the various shows laid our in your spreadsheet?
- Do you have a different spreadsheet for each show, or are they all on the same single worksheet?
- Are all of the dates for any given show on the same row?
- Or do you have as many as 300 rows for each show, where those different calculations are done, and D1 and D2 are at the top of each sheet for each row?
For now, then, if it's something like that last question, then in your formulas use $D$2 and the like. That is what's called an Absolute Reference, as opposed to a Relative Reference The latter is what you've been doing, and what gets messed up when you sort. You can learn more about those two ways of referring to other cells by clicking on the hyperlinks I provided.
There are more questions that could be asked. So if that absolute/relative reference suggestion doesn't solve it, please come back with more details about how your workbook/spreadsheet(s) are organized.
