Register
Log In
Unanswered
Home
September 26, 2016
Offline
Refer to Ticket #435170:
I cannot get to work conditional formatting for the following instance:
I want to colour a range of cells H6:BF6 based on the criteria that any one of the cells in the range H7:BF18 contain either i or c or n. That is to say that they contain something and are not empty cells.
I am trying to create a gantt chart.
Cells H6:BF18 will have an i or c, n entered to indicate "In Progress", "Complete", "Not Started" respectively. There is conditional formatting using Cell Value = "n" applied to =$H6:$BF111 that formats the cells with a fill colour. This creates a task bar in the gantt against each task.
My goal is to take the extents of those tasks (the red box in the attached pdf) i.e. the first start cell that is coloured and the last finish cell for all tasks in the range H7:BF18 and colour the row 6 (the green box in the attached pdf) to match the start and finish of the tasks that are in rows 7 to 18.
Hope that makes sense.
I have include a sample of the spreadsheet and the supporting PDF.
Kind Regards
Gary
See Answer
Dashboards
June 25, 2016
Offline
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?
September 26, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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.
Dashboards
June 25, 2016
Offline
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.
September 26, 2016
Offline
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
Dashboards
June 25, 2016
Offline
Try this
September 26, 2016
Offline
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
September 26, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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.
Dashboards
June 25, 2016
Offline
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)
September 26, 2016
Offline
Thanks SunnyKow
That has worked great.
Cheers
Gary
Dashboards
June 25, 2016
Offline
No problem 
September 26, 2016
Offline
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
September 26, 2016
Offline
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
Dashboards
June 25, 2016
Offline
Hi Gary
Just in case you are having issues, I have attached a completed one for you.
Cheers
Sunny
Most Users Ever Online: 57
Currently Online: clark shao
12 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
