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]

Okay, this is my SWAG at it. I'm guessing you want to see progress (orange) overlaid on expected timeline (red).
I do not have any named ranges in this.

Book1
	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
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
6		Jake Humpheries
7		DAN001	1	10	0.29
8		DAN002	5	10	0.25
9		DAN003	10	20	0.41
10
11			Conditional Formatting Formula (starting at cell F7)
12		Completed	=($C7<=F$5)*(($C7+($D7*$E7)-1)>=F$5)
13		Not Completed	=($C7<=F$5)*(($C7+($D7)-1)>=F$5)
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F7:AJ9	Expression	=($C7<=F$5)*(($C7+($D7*$E7)-1)>=F$5)	text	NO
F7:AJ9	Expression	=($C7<=F$5)*(($C7+($D7)-1)>=F$5)	text	NO
F7:AJ9	Expression	=ISEVEN(COLUMN(F5))	text	NO

 

[Eddie65789]

Thanks for your continued support. Indeed I have found information in the Name Manager (see below). I have to admit I am completely ignorant on this area of Excel functionality and therefore how to edit it for my needs. I am trying to create multiple months with the same functionality as the first one, currently this is duplicating.

 

[awoohaw]

Thanks for your continued support. Indeed I have found information in the Name Manager (see below). I have to admit I am completely ignorant on this area of Excel functionality and therefore how to edit it for my needs. I am trying to create multiple months with the same functionality as the first one, currently this is duplicating.

View attachment 105689

OK, well, as I suspected the named ranges were formulas. But, you've moved/deleted some cells that have made the formulas invalid (see the "REF#" portions of the formulas in the "refers to" column). This is probably why you began to get errors. This can't be fixed unless you have a back up of your original download (if you have version manager in your storage location you may have that).

But, it may not matter since you seem to be trying to tailor the workbook to different needs. Look at what I did in the prior post (POST #11) and comment please.
Please provide a clear and complete description of what colors you want to see in the calendar and what conditions are needed to make the color change.

Also, in the same sheet, since the formatting refers to mixed cell references (some fixed column, some fixed row) you will need to enter conditional formatting for each month of your worksheet, but just by editing the column and row references as necessary.