Solved

One further step in gantt chart conditional formatting

Posted on 2013-01-30
395 Views
Hi,

Am hoping this is a relatively easy query!

Have constructed gantt chart which autocolours using conditional formatting based on start and end dates for each task: =IF(AND(G\$4>=\$D6, G\$4<=\$E6),1,"") where G4 is the calendar date, D6 is the task start date and E6 is the task end date.

I would like to know if it is possible to in addition to have the conditional formatting colours on a RAG basis dependant on an additional status column ie green/completed, amber/ongoing and red/not started while still maintaining the original "base" colour to show the full timescale allocated to the task?  So in effect up until the current date would show as RAG and remainder until end of task would show as base colour.

If this is not possible any ideas on good ways to show the progress on the gantt in alternative ways?

Many thanks

Jaffa
0
Question by:jaffageek
• 4
• 3

LVL 24

Expert Comment

If you could provide a very simple workbook (just some similar non-sensitive data in your layout), we can provide an answer far easier.

This should not be too hard in 2007+ but 2003 may require some thought due to limited number of conditional format criteria allowed.

It will follow the method of conditional format based upon formula:
=A1="GREEN"
Then set fill to green
One format condition per colour.
So with original rule = 4 rules (2003 = 3 max)

So 3 rules would be more involved.
0

LVL 24

Expert Comment

Looking at your original Q I have modified the file to see if this meets your needs:

Has just a single formula applied to the whole sheet for each colour.
Gantt-Fix.xlsx
0

Author Comment

Hi,

Your example exactly the sort of thing was looking to do however unable to figure out how it works!  Does it make a difference if I have the start and end dates as inputs and the number of days as a calculation as opposed to the end date calculating from the number of days?  Your conditional format formula uses row 1 and yet there is no data in that row? i.e. A1 is "days"

Sorry if bit if a dumb question but spent 2 days trying to figure it out now
0

LVL 24

Expert Comment

OK, so it is a single formula for each colour:

=AND(\$B1<=A\$1,\$C1>=A\$1,\$D1="R",A\$1<>0)

The essence is that it is applied to ALL cells (not a single range, row etc)

So we take the =AND to form criteria that must all be met:

=AND(
\$B1<=A\$1 is the value in the current row and column B is less than or equal to Current column row 1 (the dollar before the B means that as the conditional formatting "moves" B doesn't, the same for the row 1)
,
\$C1>=A\$1 is the value in the current row and column C is greater than or equal to Current column row 1
,
\$D1="R" is the value in the current row and column D equal to "R"
,
A\$1<>0 is the value in row 1 and the current column not 0
)

If all these criteria match up then  the conditional format will trigger.
So date in row 1 is greater than in B and Less than in C and Column D is "R" and Row 1 has a value.

The use of the dollar symbol '\$' in the formula before a row or column will stop that row from incrementing as the cell being formatted is different.

Does this start to make sense... I can see how it can be a tad convoluted.
If you need furtur clarification I am happy to try fill any blanks.

:)

If you are able to define exactly what your result will look like using the file in this question I can look to provide the conditional format rules to acheive the desired results.
0

Author Comment

Thanks for your unending patience!

Have tried what seems to be a million times to add your formatting, first attempt gave me the colours but in the wrong cells (didn't match the dates).  Next attempt colour in the right cells but only one colour (blue). Next attempt absolutely no colours at all (zilch, not even blue!).

Won't bore you with the outcome of the 100 other attempts (one had split colours between blue, red and green in the same row?) so have attached the file minus some of the non essential data in the hopes you can see where am going wrong - have a feeling it is the way my workbook is set up rather then a problem with the formulas

Many thanks again
Draft-Project-Gant-Jan-2013---re.xlsx
0

LVL 24

Accepted Solution

Steve earned 400 total points
So so so close... really close...

your dates are in row 3 (not row 1) so the Dollar Signed Row should be 3...

=AND(\$D1<=A\$3,\$E1>=A\$3,\$G1="G",A\$3<>0)

That was the only required change (ammended file attached)

Of note... be sure to have cell A1 selected when creating whole range conditional formatting.
As Excel will assume the selected cell to be the "starting point" for referencing. (this is why the dates were not lining up correctly)
Draft-Project-Gant-Jan-2013---re.xlsx
0

Author Closing Comment

Comprehensively explained by a very patient and encouraging expert - even though I didn't "get" it first time round, expert perservered and solved the issue, top marks and thanks! :o)
0

Join & Write a Comment Already a member? Login.

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!