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!
<tbody>
</tbody>
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
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!
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
| 1 | Project | Scope Start | Scope End | Rqts Start | Rqts End | 9/2 | 9/9 | 9/16 | 9/23 | 9/30 | 10/7 | 10/14 | 10/21 |
| 2 | PRJ 1 | ? | ? | ? | ? | 75% | 75% | 75% | 75% | 75% | 75% | ||
| 3 | PRJ 1 | ? | ? | ? | ? | 75% | 75% | 75% | 75% | ||||
| 4 | Total | n/a | n/a | n/a | n/a | 150% | 150% | 75% | 75% | 150% | 75% | 75% |
<tbody>
</tbody>
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
