K
Karl
Hi,
I wonder if anyone can help. I’ve created a Gantt chart in Excel that
behaves more or less like a Gantt chart in Microsoft Project.
Down the left-hand side of the worksheet is a list of tasks. Each task has a
start date, an end date and a duration (the end date is arrived at by adding
the duration to the start date using the WORKDAY function).
So, thus:
=(WORKDAY(C4,E4)-1)
To the right of this task list, each column is assigned a date. The project
start date (the first column in the Gantt chart) equals the start date of the
first task. The header date for every other column in the Gantt chart is
reached by adding the value of 1 to the date of the column on the left – so
all the user has to do is enter the project start date once, in one place,
all the other dates are worked out automatically for them.
Every cell in the Gantt chart, compares the date in its column header to
start and end dates of the task in its row. If the date in the header falls
between those two date, it returns the word “yes†otherwise it returns the
word “noâ€.
So the formula looks like the one below.
=IF(AND(F2>=$C$4,F2<=$D$4),"yes","no")
The background of the Gantt chart is white and by default so is the text, so
“no†is invisible.
However, I’ve applied conditional formatting to entire chart area. Wherever
the word “yes†appears, both cell and text are formatted in the same shade of
red.
The result is a Gantt chart that populates correctly when the user enters
simply the project start date and the duration of each task.
What I want to do now, is introduce three types of task. I want each cell in
the Gantt chart not only to check its column date against duration dates for
the task, I also want it to check a validated list for the words “Task 1â€,
“Task 2†and “Task 3â€.
When the value in the row is set to “Task 1â€, if the column date falls
within the duration of the row’s task, I want the cell to return the value
“Task 1†instead of “yesâ€.
I will then use conditional formatting to create three different coloured
bars in the Gantt chart area: one colour for each task type.
Can anyone tell me if this is possible and if so, how?
Many thanks
Karl
I wonder if anyone can help. I’ve created a Gantt chart in Excel that
behaves more or less like a Gantt chart in Microsoft Project.
Down the left-hand side of the worksheet is a list of tasks. Each task has a
start date, an end date and a duration (the end date is arrived at by adding
the duration to the start date using the WORKDAY function).
So, thus:
=(WORKDAY(C4,E4)-1)
To the right of this task list, each column is assigned a date. The project
start date (the first column in the Gantt chart) equals the start date of the
first task. The header date for every other column in the Gantt chart is
reached by adding the value of 1 to the date of the column on the left – so
all the user has to do is enter the project start date once, in one place,
all the other dates are worked out automatically for them.
Every cell in the Gantt chart, compares the date in its column header to
start and end dates of the task in its row. If the date in the header falls
between those two date, it returns the word “yes†otherwise it returns the
word “noâ€.
So the formula looks like the one below.
=IF(AND(F2>=$C$4,F2<=$D$4),"yes","no")
The background of the Gantt chart is white and by default so is the text, so
“no†is invisible.
However, I’ve applied conditional formatting to entire chart area. Wherever
the word “yes†appears, both cell and text are formatted in the same shade of
red.
The result is a Gantt chart that populates correctly when the user enters
simply the project start date and the duration of each task.
What I want to do now, is introduce three types of task. I want each cell in
the Gantt chart not only to check its column date against duration dates for
the task, I also want it to check a validated list for the words “Task 1â€,
“Task 2†and “Task 3â€.
When the value in the row is set to “Task 1â€, if the column date falls
within the duration of the row’s task, I want the cell to return the value
“Task 1†instead of “yesâ€.
I will then use conditional formatting to create three different coloured
bars in the Gantt chart area: one colour for each task type.
Can anyone tell me if this is possible and if so, how?
Many thanks
Karl