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:C232036EC1A6460F8D26EF69E57570A0(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 