Basic Gantt Chart - how to use conditional formatting to highlight percentage allocation per project

[Eddie65789]

Hello,

I was hoping the community can help me. I downloaded a standard Gantt template form Excel and have begun modifying it. I would like to present 3 months of data side by side in the same worksheet (only two shown in the images).

The challenge I have is that I can't seem to replicate the conditional formatting for the Feb data, its pulling through the data from columns C- E, rather than AL-AN. I'm not sure if the formulas used in the conditional formatting can used twice in one worksheet. PIC1 has the conditional formatting setting for Jan and PIC2, the conditional formatting for Feb.

Please let me know if any further information is required.

Thanks!

 

[awoohaw]

Please list the ranges for the defined ranges or table headings. If you can provide a xl2bb of the worksheet (link below) it would really helpful. If you cannot use the add in, then please paste a list of the defined range names and address, or table.

Thanks in advance.

 

[Eddie65789]

Gantt project planner1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR
1
2						Actual (beyond plan)								% Complete (beyond plan)
3
4
5						#																																			#
6		ACTIVITY	Start	Dur.	% Alloc.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31		Start	Dur.	% Alloc.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
7		Jake Humpheries
8		DAN001	1	5	100%																																	1	25	25%
9		DAN002	1	5	100%																																	1	25	25%
10		DAN003	1	5	100%																																	1	25	25%
11
Test

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO8:BP10	Expression	=PercentCompleteBeyond	text	NO
AO8:BP10	Expression	=ActualBeyond	text	NO
AO8:BP10	Expression	=AO$6=period_selected	text	NO
AO8:BP10	Expression	=MOD(COLUMN(),50)	text	NO
AO8:BP10	Expression	=MOD(COLUMN(),50)=0	text	NO
F8:AJ10	Expression	=PercentCompleteBeyond	text	NO
F8:AJ10	Expression	=ActualBeyond	text	NO
AP6:BP6,F6:AJ7	Expression	=F$6=period_selected	text	NO
F8:AJ10	Expression	=MOD(COLUMN(),2)	text	NO
F8:AJ10	Expression	=MOD(COLUMN(),2)=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1:B2	Any value
AL6:AL7	Any value
AM6:AM7	Any value
AN6:AN7	Any value
M2	Any value
D2	Any value
E2	Any value
A1	Any value
F4:F5	Any value
B3:B7	Any value
C3:C7	Any value
D3:D7	Any value
E3:E7	Any value

 

[Eddie65789]

Please list the ranges for the defined ranges or table headings. If you can provide a xl2bb of the worksheet (link below) it would really helpful. If you cannot use the add in, then please paste a list of the defined range names and address, or table.

Thanks in advance.

Done, hopefully that helps and thanks for your patience.

 

[awoohaw]

thanks, for some reason the named ranges were not in the xl2bb paste. I'll assume E2 and M2 are "Actual" and "Complete" named ranges?

 

[Eddie65789]

thanks, for some reason the named ranges were not in the xl2bb paste. I'll assume E2 and M2 are "Actual" and "Complete" named ranges?

I amended this from a template, if I delete out line 2 it doesn't seem to make any difference to teh current functionality.

 

[Eddie65789]

Hopefully this is a little clearer. I'm attempting to have independent Gannt charts for each month, currently the data for month 1 and 2 are linked.

Gantt project planner1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ
1
2
3
4
5		ACTIVITY	Start	Dur.	% Alloc.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31		Start	Dur.	% Alloc.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
6		Jake Humpheries
7		DAN001	1	10	100%																																	1	25	25%
8		DAN002	5	10	100%																																	1	25	25%
9		DAN003	10	20	100%																																	1	25	25%
10
Test

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO7:BP9	Expression	=PercentCompleteBeyond	text	NO
AO7:BP9	Expression	=ActualBeyond	text	NO
AO7:BP9	Expression	=AO$5=period_selected	text	NO
AO7:BP9	Expression	=MOD(COLUMN(),50)	text	NO
AO7:BP9	Expression	=MOD(COLUMN(),50)=0	text	NO
F7:AJ9	Expression	=PercentCompleteBeyond	text	NO
F7:AJ9	Expression	=ActualBeyond	text	NO
AP5:BP5,F5:AJ6	Expression	=F$5=period_selected	text	NO
F7:AJ9	Expression	=MOD(COLUMN(),2)	text	NO
F7:AJ9	Expression	=MOD(COLUMN(),2)=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AL5:AL6	Any value
AM5:AM6	Any value
AN5:AN6	Any value
A1	Any value
B1	Any value
B2	Any value
C2	Any value
D2	Any value
E2	Any value
B5:B6	Any value
C5:C6	Any value
D5:D6	Any value
E5:E6	Any value

 

[awoohaw]

I still don't understand the named range references. Where are they defined? What are the rules for changing colors?
What cells are in the ranges:
"PercentCompleteBeyond"
"ActualBeyond"
"period_selected"


If these are just defined names, then you need to be sure to included the named ranges definitions.
If they are from a table, please post the table.

If you click the check box "Named Ranges" in the "Additional Info" section of the XL2BB ribbon, they should be pasted with the mini worksheet.

And a text definition of what you mean by :
"ActualBeyond"
"PercentCompleteBeyond"
and
"period_selected"
1s really needed.

 

[Eddie65789]

Hello, thanks for patience, this is a unknown are of Excel for me.

The "Named Ranges" and "Additional Info" options were selected when I initiated the mini worksheet.

As I mentioned at the start, I have altered the "Gantt Project Planner" template available in Excel.

From what I can tell, the conditional formatting is using data from columns B - D, rows 7-9 to determine the start point and end point of teh displayed orange bar.

I have never seen the formulas "PercentCompleteBeyond" and "ActualBeyond" used as arguments in the conditional formatting.

 

[awoohaw]

Those names are part of your CF rules, so they are in there.

click on name manger and see if there are any names defined?
FORMULAS ribbon, Name Manager.

What is your "idea" in text, that would make a cell PINK.
I'm assuming the orange is for the "scheduled" activity range.

And that you input the percentage complete in the percent column?