Hi,

I wonder if anyone can help. Ive 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, Ive 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 rows 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

=IF(AND(F$2=$C4,F$2<=$D4),IF($A4="Task 1","Task 1",IF($A4="Task 2","Task
2",IF($A4="Task 3","Task 3","yes"))),"no")
assuming that your "Task 1" to "Task 3" will be in column A for the row in
question.
--
David Biddulph

"Karl" wrote in message
...
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

Should be possible. I wouldn't clutter up the cells of the chart, though.
You can base your CF formulas on dates (and tasks, using David's extended
formula) without worrying about what's in the cells. This article has some
examples:

http://pubs.logicalexpressions.com/P...cle.asp?ID=343

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Karl" wrote in message
...
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

Hi,

I also made a simple Gantt chart similar to this one but I have a column for
priority number, person the task is assigned to, the task, start date, and
due date. I got the formulas and conditional formatting done.

The priority number is only unique for each person. Otherwise the numbers in
this column is not unique.

What I want to do is when a task is completed by the person, I'll put "done"
under the priority column.

Is it possible to auto-update the priority number after a task is done for
that particular person?

Is there a formula I can use?

Thanks!

Laila

"David Biddulph" wrote:

=IF(AND(F$2=$C4,F$2<=$D4),IF($A4="Task 1","Task 1",IF($A4="Task 2","Task
2",IF($A4="Task 3","Task 3","yes"))),"no")
assuming that your "Task 1" to "Task 3" will be in column A for the row in
question.
--
David Biddulph

"Karl" wrote in message
...
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