Use cell color or text font as part of index/match formula?

[mwtinec]

Hi guys, I've got a real conundrum on my hands and I'm hoping some of you really smart excel aficionados can help me.

User Goal
I have many rows of project data that I'm using for capacity planning that looks something like the table below (this is a much simpler version). I'll be providing access to this sheet on a shared network to others that I want to update with their staff allocations. The key to their willing participation is that I make this as simple and user proof as possible while still generating some value information.The conclusion I've come to is the end users needs to only worry about entering the percent staff allocation under the dates in the Dates section. This information will be used to populate the table information to the right. In fact, I'll likely hide most of the table data from their view (for safe keeping). So here's my data and question. Have at it!


[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD]Scope Start[/TD]
[TD]Scope End[/TD]
[TD]Rqts Start[/TD]
[TD]Rqts End[/TD]
[TD]9/2[/TD]
[TD]9/9[/TD]
[TD]9/16[/TD]
[TD]9/23[/TD]
[TD]9/30[/TD]
[TD]10/7[/TD]
[TD]10/14[/TD]
[TD]10/21[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PRJ 1[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PRJ 1[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Total [/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD][/TD]
[TD]150%[/TD]
[TD]150%[/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD]150%[/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[/TR]
</tbody>[/TABLE]

The bold percents (F2:M3) are associated with the Scope Dates (B2:C3) and the unblold percents with the Rqts Dates (D2:E3).

Formula Goal
How can I have formulas that:
1. finds the date (F1:M1) for the first occurrence of bold 75%
2. finds the date (F1:M1) for the last occurrence of bold 75%
3. finds the date (F1:M1) for the first occurrence of unbold 75%
4. finds the date (F1:M1) for the last occurrence of unbold 75%

Note that the example above uses bolding to illustrate the difference between Scope and Rqts percents. In my actual sheet I use fill color. I'm open to anything that allows me to:
1. Pull the correct start and end dates.
2. Allows me to sum up the percents vertically when I filter on staff name (hidden in this example).
3. I'm quite proficient with excel formulas but have no experience with VBA. If the solution is VBA then please be excruciatingly precise in your instructions.

Thanks everyone in advance for your help. Much Appreciated.

Matt

 

[mwtinec]

I've figured out how to sum vertically when the cell contains numbers and text. I'm using this formula in A4:

=SUMPRODUCT(LEFT(0&A2:A100&" ";FIND(" ";0&A2:A100&" ")-1)+0)

[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/2/13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD].75 Scope[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD].75 Scope[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.5[/TD]
[/TR]
</tbody>[/TABLE]


So, if this helps the solution I'm looking for above then please bake it in. Obviously, I'd rather not have to couple a text based identifier with the numerical data but I could live with it