Prev: Can I add to a cell value without manual calculation?
Next: Please very urgent and help
From: Dave on 31 Mar 2010 08:07
Hello,

I am trying to create a spreadsheet to track the progress of tasks in a
project. I found a good example on the web in Open Office and would like to
model my spreadsheet off of that example. However, I am new to Excel and
can't get past the conditional formating issues.

Basically my spreadsheet is laid out as follows:

Start Date: Column D beginning in cell 10
End Date: Column E beginning in cell 10

So the first task start and end dates reside in row 10 and up

I have a calender laid out from L8:AR8 (just an arbitrary length really)

Challenge 1:

I would like to (a) highlight the duration of each task and (b) show the
percent complete of each task based on a percent complete figure in column G.

For a. I first need a conditional formating statement to cover all of the
rows with a task. I have searched the in Internet and cannot get my
statement to work. I have tried a bunch but it seems to me the following
should work.

=AND($L$8>=$D$10,$L$8<=$E$10)

I then clicked FORMAT for the cell to be filled with BLUE if TRUE. Applies
to =$L$8:$AR$10

Question 1. As the bar progresses the formula should refer to the date
above the cell in the calender line in row 8. I'm not sure if the base
reference just stays $L$8 and that date is before my begin date so I never
get a bar. And how do I make it change and refer to the box above in Row 8
as the calendar progresses to the right?

Question 2: When and if I satisfy question 1 above, I would like for the
bar to be colored black for the duration and the appropriate portion colored
blue based on a percent complete figure.

Any help / advice to help me get past this roadblock is much appreciated.

Best regards,

Dave
From: Bob Phillips on 31 Mar 2010 08:26
Try

1 =AND(L$8>=$D10,L$8<=$E10)

2
=SUMPRODUCT(--($L$8:$AR$8>=$D10),--($L$8:$AR$8<=$E10))/COUNT($L$8:$AR$8)>10%

--

HTH

Bob

"Dave" <Dave(a)discussions.microsoft.com> wrote in message
news:C232036E-C1A6-460F-8D26-EF69E57570A0(a)microsoft.com...
> Hello,
>
> I am trying to create a spreadsheet to track the progress of tasks in a
> project. I found a good example on the web in Open Office and would like
> to
> model my spreadsheet off of that example. However, I am new to Excel and
> can't get past the conditional formating issues.
>
> Basically my spreadsheet is laid out as follows:
>
> Start Date: Column D beginning in cell 10
> End Date: Column E beginning in cell 10
>
> So the first task start and end dates reside in row 10 and up
>
> I have a calender laid out from L8:AR8 (just an arbitrary length really)
>
> Challenge 1:
>
> I would like to (a) highlight the duration of each task and (b) show the
> percent complete of each task based on a percent complete figure in column
> G.
>
> For a. I first need a conditional formating statement to cover all of the
> rows with a task. I have searched the in Internet and cannot get my
> statement to work. I have tried a bunch but it seems to me the following
> should work.
>
> =AND($L$8>=$D$10,$L$8<=$E$10)
>
> I then clicked FORMAT for the cell to be filled with BLUE if TRUE.
> Applies
> to =$L$8:$AR$10
>
> Question 1. As the bar progresses the formula should refer to the date
> above the cell in the calender line in row 8. I'm not sure if the base
> reference just stays $L$8 and that date is before my begin date so I never
> get a bar. And how do I make it change and refer to the box above in Row
> 8
> as the calendar progresses to the right?
>
> Question 2: When and if I satisfy question 1 above, I would like for the
> bar to be colored black for the duration and the appropriate portion
> colored
> blue based on a percent complete figure.
>
> Any help / advice to help me get past this roadblock is much appreciated.
>
> Best regards,
>
> Dave


 | 
Pages: 1
Prev: Can I add to a cell value without manual calculation?
Next: Please very urgent and help