Hi
I have made a gant chart in excel, which highlights tasks from a start date to an end date with conditional formatting.
I would now like to make a summarizing line (total), which colors days that are booked by any of the activities.
Gant chart .jpg
Any good advice?
Thanks
Hello
I'm not sure where your "Start" and "End" dates are situated but the following CF formula applied to the "Total" row should work:
Formula:
Please Login or Register to view this content.
Hope this helps.
DBY
Considering that you may have a multiple number of tasks, you might want to move the summarizing line above the dates. The following has a function, HasColor that returns true or false based on whether the range it looks at has a colored cell. I used this function in a conditional format statement on row 1. Despite the Application.Volitile, you still have to press F9 to activate the conditional format. Hmmm.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
If the Gantt chart bars have values in the cells, you could select the total row and use a formula in Conditional Formatting to SUM the columns with a formula like this in Conditional Formatting
=SUM(C2:C20)>0 Then apply the conditional formatting.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I really like this function, but it has to be used by others than me, so need a solution that auto refresh
Thanks for the reply. I am still struggling a bit with this, but I should look into the COUNTIFS function.
I think what the problem with the refresh is, is that Excel knows when you change the contents of a cell; it does not know when you change the color of a cell - at least change in color cannot be used to trigger an event as changes in content do. At best, Excel knows that it needs to save the file on close.
To use the countif function, you will need content in the cells.
@dflak
It isn't necessary to have values in the cells for Conditional Formatting. The Countifs formula I suggested can be used in the New Formatting Rules dialogue "Use a formula to determine which cells to format".To use the countif function, you will need content in the cells.
DBY
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks