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]

If I'm understanding you, then you wouldn't want to have the DataValidation(DV) applied to the range that has the workers' names, you would want to have some kind of lookup table.

How you organize that depends on how you will use the lookup. Here are a few examples....

Excel Workbook
	A	B	C	D	E
1	Project Management	Superintendent	Person Reporting
2	Ted Baker	Alice Delta	Bob Able
3	Alice Delta		Ted Baker
4			Alice Delta
5
6
7	Bob Able	Ted Baker	Alice Delta
8	Person Reporting	Project Management	Superintendent
9		Person Reporting	Project Management
10			Person Reporting
11
12
13	Labor Rates	Project Management	Superintendent	Person Reporting
14	Bob Able	#N/A	#N/A	$15
15	Ted Baker	$35	#N/A	$20
16	Alice Delta	$40	$50	$30
17
Sheet

 

[PHIntern]

These are great. I am unfamiliar with Look up functions. I will try to do some research on them. I know that the labor rates table would be useful.