Conditional Formatting: How to shade cells based on meeting 2 criteria NEED HELP ASAP

[PHIntern]

I have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.

I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.

To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.

Any help would be greatly appreciated. Need this by the end of the week!

**can somebody please tell me how to attach a file to the post?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

One of these threads might help get you started:
http://www.mrexcel.com/forum/showthread.php?t=639151

In this thread, the user wanted to add comments to each Gannt Chart cells.
http://www.mrexcel.com/forum/showthread.php?t=539316

The CF formula used in that second example was tolerant of the user transposing Start and End dates:
Formula: =OR(AND(G$2>=$C3,$D3>=H$2),AND(G$2>=$C3,AND($D3>=G$2,$D3=G$2,$D3<=H$2))
Applies to: $G$3:AE$100

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM).

You can post a small screen shot showing the layout of your Gantt Chart using one of the tools listed in my signature.

 

[PHIntern]

Thanks for the reply. I will send you a screen shot of my format first thing in the morning once I get to my office computer . Can you PM me your email?

Thanks again!

 

[Jerry Sullivan]

If possible, it would be better if you could use a tool like Excel Jeanie to post the screen shot to this thread.

That way others can benefit from the discussion and add in their suggestions.

 

[PHIntern]

I would have no problem with that but due to admin restrictions in the office I am unable to download things like Excel Jeanie. I would not be opposed to emailing it to you and you could post it for others to benefit.

thanks

 

[PHIntern]

The formula I am at is "=OR(AND($K$13>=$G$17,$H$17>=$R$13,AND($K$13>=$G$17,AND($H$17>=$K$13,$H$17=$K$13,$H$17<=$R$13))" & "=SEARCH("Project Management",$E17)"

BUT it is not applying the formatting (changing the color) of the cells in that Project Management row

 

[Jerry Sullivan]

Here's a mockup based on the jpg image you sent showing your layout.
Your image didn't show the column and row labels, so you'll need to adjust the formulas shown to match your file.

The CF formula I used is:
=AND(SEARCH("Project Management",$E7),OR(AND(I$4>=$F7,$G7>=J$4),AND(I$4>=$F7,AND($G7>=I$4,$G7=I$4,$G7<=J$4))))

Applies to: $I$7:$DZ$1000

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1					Week1							Week2
2	Title	Start	Finish
3					(5/28/12-6/3/12)							(6/4/12-6/10/12)
4					28	29	30	31	1	2	3	4	5	6	7	8	9	10
5
6
7	Project Management	29-May-12	3-Jun-12
8	Superintendent	29-May-12	3-Jun-12
9	Person Reporting	29-May-12	3-Jun-12
10	Person Reporting	29-May-12	3-Jun-12
11
12	Project Management	31-May-12	7-Jun-12
13	Superintendent	29-May-12	1-Jun-12
14
Sheet

Sometimes when trying to find a CF formula that gives the desired result, it's helpful put the formulas directly in the Cells to see if they evaluate to TRUE or FALSE. For solving a problem like you described in your original post, this allows you to use Excel's Evaluate Formula dialog tool to track down errors in your formula.

Here's a snip of what that looks like for this formula...

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O
1					Week1
2	Title	Start	Finish
3					(5/28/12-6/3/12)
4					28	29	30	31	1	2	3
5
6
7	Project Management	29-May-12	3-Jun-12		FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
8	Superintendent	29-May-12	3-Jun-12		#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
9	Person Reporting	29-May-12	3-Jun-12		#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
10	Person Reporting	29-May-12	3-Jun-12		#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
11					#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12	Project Management	31-May-12	1-Jun-12		FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE
13	Superintendent	29-May-12	1-Jun-12		#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
Sheet

 

[PHIntern]

This worked like a charm!

Many thanks!!

 

[Jerry Sullivan]

Glad the helped.

One other thought...

On the image you sent, you had a column "wd" for workdays. (not shown in my mockup).
It looks like that was based on a 7 day work week which is not typical for construction schedules.

If that isn't your intent, you might want to use the worksheet function NETWORKDAYS() which allows you to account for weekends and holidays.

 

[PHIntern]

In the "normal" construction world you are correct, but we are involved in a lot of retail work so weekend work is pretty common.

I do have another problem though. Several of these guys or "resources" can play several roles like a PM, or Super, or even a General Contractor.

On the second sheet I would like to have my resources listed, having the capability to have a drop down box on each name listing all of their capabilities so we can see what they are able to do on a specific job.

I have tried doing a drop box directly on the name and then making a data validation list. However, when I click the task the name is replaced with the task.

Thoughts??