Hi Gary

Can you provide the expected result in your attachment (what colours are expected to be seen in row 6) based on the data in the PDF?

Hi SunnyKow

It would be great if (as can be seen in the attached PDF) Row 6 could be coloured based on the content of the rows underneath.

1. If all rows (7 - 18) beneath are the red colour for "not started" then row 6 would be the red colour for "not started"

2. If all rows (7 - 18) beneath are a mix of colours then row 6 would be the orange colour for "in Progress"

3. If all rows (7 - 18) beneath are the green colour for "completed" then row 6 would be the red colour for "completed"

If that is not possible then we could specify any colour just to indicate the extent of the overview task in row 6.

A really nice option (but very difficult to do I think) would be to display progress in row 6 based on how many tasks are completed in rows 7 - 18. That would be an overall bar in row 6 to so the start and finish, and have a bar extending from the start as progress increases. This I have tried to show in the fourth table in the new attachment.

Thank you for your time.

Gary

Hi Gary

I think the progress bar is possible if you update the Status in column G. You can then do a count of Completed against the total Task assigned to get the % completed. You can then plot a progress bar with that. For the rest, my suggestion is to count the number of non empty cells in the range H6 to BF18 and compare it against the count of C or N. If there are no differences for any of them, then you can safely assume that the task is C or N. If there are any differences then there are a mixture of status and you can assume it to be in Progress (I).

If you update the Status in column G, it will be even easier to ascertain the status instead of counting the range of H6 to BF18.

Hope this helps.

Hi Gary

I have some suggestions. I have done some for you to take a look.

The formula for the CF you wanted is quite long but workable. All is based on the Status in column G.

I have merged the cells in row 6 etc. so that is is easier to manage with CF.

My other suggestions are in columns BH and BI. This way you can see all in one go.

Hope this will be useful to you.

Hello SunnyKow

Thank you for the spreadsheet. You have done a good job but there is one thing that is not quite right.

Row 6 colouring needs to start and finish at the first start point of the rows below and finish at the last end point of the rows below, not the whole of Row 6 coloured. This is intended to show the duration of the tasks from the start date to the finish date.

I do not know if that is possible?

Kind Regards

Gary

Try this

Hi

That looks like what I was after, I will work with this.

Thank you again for your time, it is very appreciated and has saved me a lot of time.

Have a great day

Gary

Hello SunnyKow

I have run into a little problem.

I tried adding the conditional formatting in Row 52 the same as for Row 6 that shows the coloured bar from the start date to the finish date.

Except in Row 52 cells BF52 & BK52 are included in the colour bar and I cannot work out why.

I have removed all the conditional formatting and did it again except what you did for Rows, 6, 21 & 30.

I have attached the spreadsheet again, would you mind trying to find what is happening and I can fix it and avoid the same happening again.

Thanks very much,

Gary

Hi Gary

Copy one of the conditional formatting rows and paste it to where you want and then change the conditional formatting ranges as needed.

My columns are merged eg IJK, LMN etc while yours are not.

Just change the following conditional formatting rule in bold to your required ranges.

=AND($BK$30="Not Started",(I$4+J$4+K$4)<=$F$30)

=AND($BK$30="Complete",(I$4+J$4+K$4)<=$F$30)

=AND($BK$30="In Progress",(I$4+J$4+K$4)<=$F$30)

Thanks SunnyKow

That has worked great.

Cheers

Gary

No problem 

Hi SunnyKow

The same issue has happened.

I removed all the previous conditional formatting rules that impacted the cells in question, merged the cells in Row 52 (just like yours in Row 30 for example) and then tried to apply the conditional formatting for "Not Started" to Row 52.

I selected cell F52, then did <Conditional Formatting> <This Worksheet> <New Rule> <Using a Formula>

Copied =AND($BK$52="Not Started",(I$4+J$4+K$4)<=$F$52) into the formula box, then set the colour to be displayed, then set the <Applies To> by using the Button on the end of the <Applies To> box, then selecting Row 52 from I52 to BG52, then accepting that, then applying the conditional format.

The result is again in the attached spreadsheet. I have removed all formatting from the cells in question and reapplied the above steps with sometimes different results, for example the first "merged cells" at I52,J52,K52 are missed from the formatting and then the formatting begins from the next merged cell at L52.

Now I am totally confused, and would appreciate any more help you might be able to provide to enable me to achieve my outcome.

Thaks

Gary

Hi SunnyKow

I think I have found a workaround.

I deleted all rows from row 52 to row 124, then copied a section of rows you had set up (rows 30 to 51), then insert those rows after row 51, then changed a couple of the formula.

This seems to have retained the correct conditional formatting.

I am hoping that this is now going to work for me.

Cheers

Gary