Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 114
Default Gannt chart: formulas and conditional formatting

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


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Gannt chart: formulas and conditional formatting

=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




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6,582
Default Gannt chart: formulas and conditional formatting

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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Gannt chart: formulas and conditional formatting

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a gantt chart using formulas/conditional formatting Karl Excel Discussion (Misc queries) 1 May 3rd 08 06:14 PM
Conditional Formatting for Formulas Murat Gordeslioglu Excel Discussion (Misc queries) 3 June 22nd 06 09:03 AM
how do i create gannt chart in excell? Deepak Charts and Charting in Excel 1 April 7th 06 12:39 PM
Conditional Formatting - Formulas meandmyhorse Excel Discussion (Misc queries) 2 February 18th 06 01:58 PM
Help Using Formulas in Conditional Formatting Still Learning Excel Discussion (Misc queries) 2 January 28th 05 05:55 PM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"