Greetings,
Firstly I want to say thank you for posting such informative videos online. They have been extremely helpful!! I have been using the Interactive Excel Project Management Dashboard – Free Download YouTube video to reference quite a bit.
I am currently designing a long range roadmap calendar and am trying to create a conditional formatted formula that would pull data from the Data Sheet that if all conditions were true it would color the applicable boxes with a designated color and Title in the spreadsheet Sheet1. I added my weekends and holidays (but couldn’t get it to post like yours with them overlapping). In my case I want to keep the weekend holiday format and also have the TRUE conditions also reflected if they fall in a weekend or holiday.
In an effort to make the conditional formatting to work, I was breaking each conditional format down to individual rows. For example, Sheet1, B4:CP4 was the selected area to apply the first conditional formatting rule. Ultimately what I was hoping to achieve was IF the swimlane reads Commander AND the Legend reads 12, THEN the applicable dates START to END would be formatted with a specific color and the TITLE would be reflected within the colored blocks. I’ve tried multiple variations of the below formula without any luck.
=IF(AND(DATA!A$2=Commander,Data!H$2=12)+Data!E$2>=’Sheet1’B$3,WORKDAY.INTL(B$3,Data!F$2,1)-1>=Data!E$2)
If this works, I would then add conditional formatting rules to each row, changing the information to make TRUE statements and formatting colors based upon the Legend. Ultimately, when adding more information in the data sheet, and if it met the basic criteria to make a statement TRUE, it would then highlight the applicable dates in that row. So each row would have multiple TRUE conditions and in turn make the gantt style timeline roadmap. I hope this makes sense. Any help is appreciated. Thank you!
Attached is my working Calendar with the example in sheet1 manually highlighted as an example to show what my end state would look like. Also, I am using Microsoft Excel 2016.
Hi Shannon,
Welcome to our forum!
The first rule of conditional formatting is that you can't layer them. i.e. only one conditional format will be applied to a cell, and the first one to evaluate as TRUE wins, which is why the order of the rules is often important.
This means that if you want to highlight weekend dates with a different format to weekday dates you need two rules, one for each format. If you want different colours for the swimlanes, then you'll need two rules for each swimlane; one for weekdays and another for weekends.
Looking at your formula rule:
=IF(AND(DATA!A$2=Commander,Data!H$2=12)+Data!E$2>=’Sheet1’B$3,WORKDAY.INTL(B$3,Data!F$2,1)-1>=Data!E$2)
I don't understand what you're trying to do here, so perhaps you can write in English what it is you want to test for, and I can help you from there.
Mynda
Greetings Mynda,
Thank you for responding to my post.
Attached is an updated spreadsheet where I manually colored in the J5 Long Range Calendar to show what I am visually trying to accomplish from my data.
I am trying to create a formula/(s) that would take the information from the Data sheet and populate J5 Long Range Calendar with the designated colors that correspond with the numbers in the legend and then label from the Title column on the specific (Start, End) dates.
Am I supposed to create a pivot table in tab format and then adjust fields from there in order to make this work properly? Then as I add more fields into the Data sheet and Refresh, it will automatically update the J5 Long Range Calendar based upon the formula criteria and conditional formatting in each row?
I am not even sure what I am trying to accomplish is possible. The closest things I have seen to this are the project management charts and gantt charts, but those still have the info on the same sheets they are pulling the data from. Thanks again for the help.
Respectfully,
Shannon Davis
Hi Shannon,
In the attached file on the Pivot sheet you'll see I've used a PivotTable to summarise the data, then added conditional formatting for the first two colours and the weekend days to columns H through BE.
I have used Dynamic Array formulas, which require Office 365/Microsoft 365, so if you see formula errors it will be because you don't have a version of Excel that works with these functions. Let me know if that's the case.
I hope that points you in the right direction and you can create the formats for the other colours based on this example. More on conditional formatting with formulas here.
Mynda
Hi Mynda,
Thank you for this additional help. I really appreciate it. I'm going to play around with this and see if it meets the criteria for my boss. Fingers crossed!! This has been a nightmare trying to find something that is easy to use and shows what he wants. Thank you again!!
Shannon
