Gantt Chart

[gr8eagle]

I downloaded a simple gantt chart from the web, but I cannot determine how the bars are being created. Here's what I do know:

Description of chart in simplest form:
- 6 columns
(1) Task (text)
(2) Scheduled Start Date (date)
(3) Scheduled End Date (date)
(4) Actual Start Date (date)
(5) Actual End Date (date)
(6) Column w/ date heading followed by additional columns with date+1 (like a calendar)

- 2 rows
(1) Headings (text or date)
(2) Task 1 row
-- Task text and date rows are actually merged cells
-- The cells beginning with the date column is represented by 2 distinct cells

- Mechanics
o When sch. start and end dates are entered in their respective columns, the corresponding cell(s) in the calendar section turns gray
o When act. start and end dates are entered in their respective columns, the corresponding cell(s) in the calendar section turns black
o Remember, a row actually consists of 2 cells on top of each other; hence, if the corresponding scheduled and actual start and end dates are the same, then the top cell would be gray and the bottom would be black.
- No formulas, other than the date+1 for the calendar heading appear when I do a CTRL+`

- I cannot alter the colors of the gantt bars w/ format cells/patterns
- I can replicate the gantt by copying the gantt cells and pasting special format
- The scheduled and actual dates are on the same row, but different columns, yet values entered in the scheduled cells apply only to top cells and values entered in the actual date fields yield results in the bottom gantt cells

WHAT'S THE MAGIC DRIVING THE GANTT BARS???

Thanks in advance for your consideration.

If someone wants to see the actual chart, let me know where to email it.

[lenze]

Pretty sure the color changes are driven by Conditional Formatting. I have a similar one. Post your email or put it in a private message and I will send you mine with the documentaion.

[gr8eagle]

Lenz,

You're absolutely correct about the conditional formatting. I've never used it, but it can definitely be powerful. Here's the formula

FORMULA IS=AND(I$3>=$E4,I$3<=$F4)

Any suggestions on how I can get my arms around using conditional formats and maybe finding a list of more common ones to guide me as examples?

Thanks.

[Duane]

On the very front page of this website, http://www.mrexcel.com (scroll down from top) there is a "Tip of the Week" with a step-by-step process for creating Gantt charts, including screen views.

Go through this for the details on conditional formatting formulas.

[Duane]

Here's a link directly to the article on creating Gantt charts using conditional formatting...

http://www.mrexcel.com/tip058.shtml