Forum Discussion
Conditional Formatting controlled by Drop Down
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
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,
Martin
Hello 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
- Thank you kindly Martin_Weiss, I will try this method. I have never used Power Query before so this will be a great learning experience. I appreciate you taking the time to help me.
