Forum Discussion
Solved
Conditional Formatting controlled by Drop Down
Greetings,
I have a spreadsheet that has multiple tabs that display date ranges as blackout dates for various application deployment. What I want to do is to have a drop down on the main tab that contains the name for each app, and when I select a specific app, the main sheet sets off a Conditional Formatting where the dates/cells within each apps corresponding blackout date, turn black. I have tried various formulas such as IF(AND), AND(), OR(), and others but can't seem to get this to work.
I have made multiple searches, but have come up empty on a solid workaround.
If anyone has an idea of how to make this works, I would be grateful for an input.
thanks for the screenshots. Ok, this is a bit of a challenge 😉
I prepared a proposal in the attached file. It requires that you maintain all the blackout date ranges in a single table, like this:
This table gets loaded into Power Query and gets transformed to a list with all single blackout dates and then loaded back into Excel, like this:
If you change or add apps or date ranges in the blue table, all you need to do is to refresh the green table with a right-click.
That green table is then the base for the conditional formatting rules. As you have now every single relevant day per app, you can easily setup the appropriate rules, e.g.
=COUNTIFS($R$5:$R$232,$B$2,$S$5:$S$232,A5)<>0I hope, this helps.
Kind regards,
Martin
6 Replies
it would help a lot if you could upload a sample file, so we can see what the sheet structure looks like and provide an appropriate solution.
Kind regards,
MartinHello Martin_Weiss, thanks for taking the time to look at this. I did not realize that the system does not allow for .xlsx file to be uploaded so here are screenshots. The fist image is the main sheet, second the legend and lists, third, fourth, and fifth the blackout dates, and the final the end state after selecting each app in cell B2. Hope that shows my dilemma.
    
thanks for the screenshots. Ok, this is a bit of a challenge 😉
I prepared a proposal in the attached file. It requires that you maintain all the blackout date ranges in a single table, like this:
This table gets loaded into Power Query and gets transformed to a list with all single blackout dates and then loaded back into Excel, like this:
If you change or add apps or date ranges in the blue table, all you need to do is to refresh the green table with a right-click.
That green table is then the base for the conditional formatting rules. As you have now every single relevant day per app, you can easily setup the appropriate rules, e.g.
=COUNTIFS($R$5:$R$232,$B$2,$S$5:$S$232,A5)<>0I hope, this helps.
Kind regards,
Martin
