One solution, depending on your situation, might be this. In the top
few rows of your worksheet, type in what the due dates are - titles.
Below them, use the formula similar to those suggested by Barb. Let's
assume the following scenario:
* You have a worksheet called ProjectStartDates
* Column A of ProjectStartDates worksheet contains the project name
* Column B of ProjectStartDates contains the project's start date
* Due date is always 30 days after start date
* You have a data sheet on another worksheet, called ProjectData,
where you want to have the due dates displayed across the top
* Row 1 of ProjectData contains the project's name
* Row 2 of ProjectData needs to have the due date
* ProjectData is a long worksheet
Let's say "ProjectOne" is the name of the project, and it is started
on October 1, 2007. This data is in ProjectStartDates. On
ProjectData worksheet, you have these values (omit double quotation
marks):
A1 = "ProjectOne"
A2 = (formula) "=INDEX(ProjectStartDates!$B:$B,MATCH(A
$1,ProjectStartDates!$A:$A,0))+30"
Enter all your project names in row 1 of ProjectData, and copy over
the formula in A2 - the reference will propagate correctly. Freeze
panes so that the top two rows are always showing, regardless of how
far down you scroll in ProjectData worksheet (it's long, remember) -
select cell A3 on ProjectData, and use Window->Freeze Panes command to
ensure the due dates stay in top row. You may want to unlock the data
cells and protect the workbook, to ensure the formulas remain intact.
Finally, use conditional formatting as described in Barb's post, to
highlight due dates coming up. I would add a second condition to
highlight differently (perhaps in red) for projects that are overdue.
You can obviously expand on this idea by adding flexible due dates,
etc, in a different column. For example, if your column C contains
the number of days until the project's due date from its start date,
the formula will be this (omit double quotation marks):
"=INDEX(ProjectStartDates!$B:$B,MATCH(A$1,ProjectStartDates!$A:$A,0))
+INDEX(ProjectStartDates!$C:$C,MATCH(A$1,ProjectStartDates!$A:$A,0))"
Additionally - this was the first thing that came to mind when I saw
the subject of your post - you can use VBA to call up reminders every
so often of projects whose due dates are coming up. This is an option
when you are working in a VBA-friendly environment with macros
enabled. For instance, each time the workbook is opened, you can pop
up a dialog box listing all the project coming up within a certain
period of time, and their due dates. The data for this would, again,
come from the ProjectStartDates worksheet.
