I have a sheet (The Milestone Sheet) that is made up mostly of cells linked to another sheet (The Schedule).
The 'start date' column in the Milestone sheet is referencing the 'due date' column in the Schedule. I set the 'duration' column in the Milestone sheet to 0 for every row, which makes the due date the same as the start date (since you can't reference other cells in a due date column). It also converts every task into a milestone.
The problem is - if I need to delete a row from the schedule, it remains in the Milestone sheet. The links are broken, but the data stays in there. My workaround for that was going to be to clear the data from the entire row in the Schedule before deleting it. When I do that, it works for every column except for the dates, which stay there! I'd like the Milestone sheet to follow the Schedule sheet without having to go back and forth. It works great except when I need to delete a task.
Any ideas?
Thanks!
[The reason I'm doing all of this is because all my Milestone sheets are feeding a report for forecasting. I originally tried doing this directly from the schedules, but those are color-coded by Task category, and I need the Forecast Report to be color coded by project, and you can't format Reports. So I change the color of the Milestone sheets to achieve this. When the dates don't get deleted, they still show up in the Forecast Report, even if they've been deleted from the schedule. As I'm typing this, I realize I could have the Report filter out any blank tasks. But that wouldn't work in cases where I need to keep the task, but remove the dates - for example, if a task is indefinitely postponed.]
